<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class quality_report_service{
	private $CI;
	function __construct() { 
		$this->CI = &get_instance();
		$this->CI->load->model('Class_model');
		$this->CI->load-> model('Quality_report_model');
		$this->CI->load-> model('Teacher_info_model');
		$this->CI->load ->model('Attend_model');
		$this->CI->load ->library('PHPWord/PHPWord');
	}

	//表一 获取各二级学院需听课教师数
	public function getTableList1($params){
		//查询各二级学院(部)需听课教师数
		$connar['orderby'] = "field(course_units,'商贸学院','机电工程学院','设计与艺术学院（腾讯云国际互联网学院）','建筑工程学院','财经学院','交通学院','鉴湖学院（纺织学院、黄酒学院、健康学院）','公共基础教学部（体育部）、马克思主义学院')";
		// $connar['where']=$this->CI->sqlEscape("db_teacher_info.academic_year=?? and db_admin.status = 1 and role_id = 2",array(date("Y",strtotime($params['start_month']))));
		$connar['where']=$this->CI->sqlEscape("db_teacher_info.academic_year=?? ",array(date("Y",strtotime($params['start_month']))));
		// $connar['join']=array('db_admin','db_admin.usercode=db_teacher_info.usercode and db_admin.username=db_teacher_info.username');
		$connar['items']='db_teacher_info.*';
		$teacherlist = $this ->CI-> Teacher_info_model -> get_list_full($connar);
		// echo $this->CI->db->last_query();
		//统计各分院的总人数
		$list = array();
		foreach($teacherlist as $k=>$v){
			$list[$v['course_units']][]=$teacherlist[$k]; 
		}
		$table1 = array();$table11 = array();
		$i=0;
		
		foreach($list as $k=>$v){
			$table11[$k] = count($v);
			$table1[$i]['key'] = $i+1;
			$table1[$i]['name'] = $k;
			$table1[$i]['value'] = count($v); 
			$i++;
		}
		$table1[count($list)+1]['key'] = '';//count($list)+1
		$table1[count($list)+1]['name'] = '合计';
		$table1[count($list)+1]['value'] =count($teacherlist);

		return array('table1'=>$table1,'table11'=>$table11);
	}

	//表二 xxx年度课堂教学评价去听课总体情况表
	public function getTable2List($Qualitydetail){
		$table2list = $this->getTable1SQl(array('start_time'=>$Qualitydetail['start_time'],'end_time'=>$Qualitydetail['end_time'],'listen_type'=>$Qualitydetail['listen_type']));
		// var_dump($table2list);
		$table2 = array();
		foreach($table2list as $k=>$v){
			if($k+1 == count($table2list)){
				$table2[$k]['key'] = '合计';
				$table2[$k]['name'] = '';
			}else{
				$table2[$k]['key'] = $k+1;
				$table2[$k]['name'] = $v['course_units'];
			}
			
			
			$table2[$k]['value1'] = $v['attend_count'];
			$table2[$k]['value2'] = $v['attend_count_school'];
			$table2[$k]['value3'] = $v['avg_school_score'];
		}
		
		$table2Total['table2total1'] = $table2list[count($table2list)-1]['attend_count'] + $table2list[count($table2list)-1]['attend_count_school'];
		$table2Total['table2total2'] =$table2list[count($table2list)-1]['attend_count'];
		$table2Total['table2total3'] =$table2list[count($table2list)-1]['attend_count_school'];
		$table2Total['table2total4'] =$table2list[count($table2list)-1]['avg_total_score'];
		$table2Total['table2total5'] =$table2list[count($table2list)-1]['avg_school_score'];
	

		// var_dump($table2list);
		$new_a = array(); $new_b = array();$new_c = array();$new_d = array();
		foreach($table2list as $k=>$v){
			if($v['course_units'] !='合计'){
				$new_a[$v['attend_count']] = $v['course_units'];
				$new_b[ $v['attend_count_school']] =$v['course_units'];
				$new_c[$v['course_units']] = $v;
				$new_d[$v['avg_school_score']] = $v['course_units'] ;

			}else{
				unset($table2list[$k]);
			}
		}
		$attend_count = array_column($table2list,'attend_count');
		$school_count = array_column($table2list,'attend_count_school');
		$avg_school_score = array_column($table2list,'avg_school_score');
		// var_dump($table11['设计与艺术学院（腾讯云国际互联网学院）']);
		array_multisort($attend_count,SORT_DESC,SORT_NUMERIC);//二级督导听课次数排序从大到小
		array_multisort($school_count,SORT_DESC,SORT_NUMERIC);//校督导听课次数排序
		array_multisort($avg_school_score,SORT_DESC,SORT_NUMERIC);//校督导听课平均分排序
		$attend_count_Arr =  array(
			//二级督导听课次数最多,最少
			'max_attend_name'=>$new_a[$attend_count[0]],
			'max_attend_value'=>$attend_count[0],
			'min_attend_name'=>$new_a[$attend_count[count($attend_count)-1]],
			'min_attend_value'=>$attend_count[count($attend_count)-1],
			//校督导听课次数最多,最少
			'max_school_name'=>$new_b[$school_count[0]],
			'max_school_value'=>$school_count[0],
			'min_school_name'=>$new_b[$school_count[count($school_count)-1]],
			'min_school_value'=>$school_count[count($school_count)-1],
			//校督导平均分最多,最少
			'max_schoolavg_name'=>$new_d[$avg_school_score[0]],
			'max_schoolavg_value'=>$avg_school_score[0],
			'min_schoolavg_name'=>$new_d[$avg_school_score[count($avg_school_score)-1]],
			'min_schoolavg_value'=>$avg_school_score[count($avg_school_score)-1],
			'course_units_count' =>8, //count($new_a),
		);

		return array('attend_count_Arr'=>$attend_count_Arr,'new_c'=>$new_c,'table2Total'=>$table2Total,'table2'=>$table2);
	}

	//表二  xxx年度课堂教学评价总体情况被听课(授课老师)表统计sql语句获取统计数据
	public function getTable1SQl($Qualitydetail){
		$listen_type = $Qualitydetail['listen_type'];
		$sql = "SELECT group_concat( DISTINCT db_attend.id ) attend_id,db_attend.course_units FROM db_attend  WHERE db_attend.status = ".AttendStatus::Submit[XPHEnum::Value]."  GROUP BY course_units ORDER BY field( course_units, '商贸学院', '机电工程学院', '设计与艺术学院（腾讯云国际互联网学院）','建筑工程学院','财经学院','交通学院','鉴湖学院（纺织学院、黄酒学院、健康学院）','公共基础教学部（体育部）、马克思主义学院' ) ";
		$list = $this->CI->Attend_model->query($sql);
		$str = '';
		$new_a = array();$new_b = array();
		foreach($list as $key=>$value){
			//需听课人数
			$xutingkerenshu = $this->CI->Teacher_info_model->query("SELECT count(usercode) as xtk_num from db_teacher_info where course_units = '".$value['course_units']."' and academic_year = '".date("Y",strtotime($Qualitydetail['start_time']))."'");
			$list[$key]['xtk_num'] = isset($xutingkerenshu[0]['xtk_num'])?$xutingkerenshu[0]['xtk_num']:'0';
			// 被分院督导听课人数 --本分院
			$sql1 ="SELECT count(DISTINCT usercode) as unitsupervisor_num from (SELECT * from db_teacher_info t WHERE t.course_units = '".$value['course_units']."' and t.academic_year = ".date("Y",strtotime($Qualitydetail['start_time'])).") a LEFT JOIN db_attend a on a.teacher_code = a.usercode
			where a.listen_time >= ".strtotime($Qualitydetail['start_time'])."  AND a.listen_time <= ".strtotime($Qualitydetail['end_time'] )." and a.listen_type='".$listen_type."' and a.type = ".AttendType::Unit[XPHEnum::Value]." and a.`status` = ".AttendStatus::Submit[XPHEnum::Value].";";
			// echo $sql1;
			$unitsupervisor_num = $this->CI->Attend_model->query($sql1);
			$list[$key]['attend_num'] = isset($unitsupervisor_num[0]['unitsupervisor_num'])?$unitsupervisor_num[0]['unitsupervisor_num']:'0';	

			// 被分院督导听课次数 --本分院
			$sql2 ="SELECT count(*) as unitsupervisor_count from (SELECT * from db_teacher_info t WHERE t.course_units = '".$value['course_units']."' and t.academic_year = ".date("Y",strtotime($Qualitydetail['start_time'])).") a LEFT JOIN db_attend a on a.teacher_code = a.usercode
			where a.listen_time >= ".strtotime($Qualitydetail['start_time'])."  AND a.listen_time <= ".strtotime($Qualitydetail['end_time'] )." and a.listen_type='".$listen_type."' and a.type = ".AttendType::Unit[XPHEnum::Value]." and a.`status` = ".AttendStatus::Submit[XPHEnum::Value].";";
			// echo $sql1;
			$unitsupervisor_count = $this->CI->Attend_model->query($sql2);
			$list[$key]['attend_count'] = isset($unitsupervisor_count[0]['unitsupervisor_count'])?$unitsupervisor_count[0]['unitsupervisor_count']:'0';	


			// 被校督导听课人数  	
			$sql4 ="SELECT count(DISTINCT usercode) as schoolsupervisor_num from (SELECT * from db_teacher_info t WHERE t.course_units = '".$value['course_units']."' and t.academic_year = ".date("Y",strtotime($Qualitydetail['start_time'])).") a LEFT JOIN db_attend a on a.teacher_code = a.usercode
			where a.listen_time >= ".strtotime($Qualitydetail['start_time'])."  AND a.listen_time <= ".strtotime($Qualitydetail['end_time'] )."  and a.listen_type='".$listen_type."' and a.type = ".AttendType::School[XPHEnum::Value]." and a.`status` = ".AttendStatus::Submit[XPHEnum::Value].";";
			$schoolsupervisor_num = $this->CI->Attend_model->query($sql4);
			$list[$key]['attend_num_school'] = isset($schoolsupervisor_num[0]['schoolsupervisor_num'])?$schoolsupervisor_num[0]['schoolsupervisor_num']:'0';		
			

			// 被校督导听课次数  	
			$sql5 ="SELECT count(*) as schoolsupervisor_count from (SELECT * from db_teacher_info t WHERE t.course_units = '".$value['course_units']."' and t.academic_year = ".date("Y",strtotime($Qualitydetail['start_time'])).") a LEFT JOIN db_attend a on a.teacher_code = a.usercode
			where a.listen_time >= ".strtotime($Qualitydetail['start_time'])."  AND a.listen_time <= ".strtotime($Qualitydetail['end_time'] )."  and a.listen_type='".$listen_type."' and a.type = ".AttendType::School[XPHEnum::Value]." and a.`status` = ".AttendStatus::Submit[XPHEnum::Value].";";
			$schoolsupervisor_count = $this->CI->Attend_model->query($sql5);
			$list[$key]['attend_count_school'] = isset($schoolsupervisor_count[0]['schoolsupervisor_count'])?$schoolsupervisor_count[0]['schoolsupervisor_count']:'0';		
			
			//校督导听课总平均分
			$sql6 ="SELECT avg(score) avg FROM `db_attend` LEFT JOIN ( SELECT * from db_teacher_info t WHERE t.course_units = '".$value['course_units']."' and t.academic_year = ".date("Y",strtotime($Qualitydetail['start_time'])).") a ON `a`.`usercode`=`db_attend`.`teacher_code` WHERE `a`.`course_units` = '".$value['course_units']."' and `status` = '".AttendStatus::Submit[XPHEnum::Value]."' and `type` = '".AttendType::School[XPHEnum::Value]."'  and `listen_time` >= ".strtotime($Qualitydetail['start_time'])." and `listen_time` <= ".strtotime($Qualitydetail['end_time'] )." and `db_attend`.`listen_type` = '".$listen_type."'";
			$schoolsupervisor_avg = $this->CI->Attend_model->query($sql6);
			$list[$key]['avg_school_score'] = isset($schoolsupervisor_avg[0]['avg'])? sprintf("%.2f", $schoolsupervisor_avg[0]['avg']):'0';		
			
			//总平均分
			$sql7 = "SELECT avg(score) avg FROM `db_attend` LEFT JOIN ( SELECT * from db_teacher_info t WHERE t.course_units = '".$value['course_units']."' and t.academic_year = ".date("Y",strtotime($Qualitydetail['start_time'])).") a ON `a`.`usercode`=`db_attend`.`teacher_code` WHERE `a`.`course_units` = '".$value['course_units']."' and `status` = '".AttendStatus::Submit[XPHEnum::Value]."'  and `listen_time` >= ".strtotime($Qualitydetail['start_time'])." and `listen_time` <= ".strtotime($Qualitydetail['end_time'] )." and `db_attend`.`listen_type` = '".$listen_type."'";
			$total_avg = $this->CI->Attend_model->query($sql7);
			$list[$key]['avg_total_score'] = isset($total_avg[0]['avg'])? sprintf("%.2f", $total_avg[0]['avg']):'0';	
			
			//被校督导评分90分以上的人数
			$sql8 = "SELECT	COUNT( DISTINCT teacher_code ) AS max90,att.course_units FROM (SELECT * from db_teacher_info t WHERE t.course_units = '".$value['course_units']."' and t.academic_year = ".date("Y",strtotime($Qualitydetail['start_time'])).") a LEFT JOIN db_attend att on att.teacher_code = a.usercode WHERE att.course_units = '".$value['course_units']."' and att.type = ".AttendType::School[XPHEnum::Value]." AND att.`status` = ".AttendStatus::Submit[XPHEnum::Value]." AND att.score >= 90  AND att.listen_time >= ".strtotime($Qualitydetail['start_time'])." AND att.listen_time <= ".strtotime($Qualitydetail['end_time'] )." AND att.listen_type = '".$listen_type."' 	GROUP BY att.course_units ";
			$max_90 = $this->CI->Attend_model->query($sql8);
			$list[$key]['max90'] = isset($max_90[0]['max90'])?$max_90[0]['max90']:'0';
			
			//被校督导评分80分以下的人数
			$sql9 = "SELECT	COUNT( DISTINCT teacher_code ) AS min80,att.course_units FROM (SELECT * from db_teacher_info t WHERE t.course_units = '".$value['course_units']."' and t.academic_year = ".date("Y",strtotime($Qualitydetail['start_time'])).") a LEFT JOIN db_attend att on att.teacher_code = a.usercode WHERE att.course_units = '".$value['course_units']."' and att.type = ".AttendType::School[XPHEnum::Value]." AND att.`status` = ".AttendStatus::Submit[XPHEnum::Value]." AND att.score < 80  AND att.listen_time >= ".strtotime($Qualitydetail['start_time'])." AND att.listen_time <= ".strtotime($Qualitydetail['end_time'] )." AND att.listen_type = '".$listen_type."' 	GROUP BY att.course_units ";
			$min_80 = $this->CI->Attend_model->query($sql9);
			$list[$key]['min80'] = isset($min_80[0]['min80'])?$min_80[0]['min80']:'0';
		}

		$attend_num_unit=0;
		$attend_count_unit=0;
		$attend_num_school=0;
		$attend_count_school=0;
		$avg_school_score=0;
		$avg_total_score=0;

		if($list){

		foreach ($list as $k => $v) {
				$attend_num_unit+=$v['attend_num'];
				$attend_count_unit+=$v['attend_count'];
				$attend_num_school+=$v['attend_num_school'];
				$attend_count_school+=$v['attend_count_school'];
				$avg_school_score+=$v['avg_school_score'];
				$avg_total_score+=$v['avg_total_score'];
			}

			$count = count($list);

			$list[$count]['course_units']= '合计';
			$list[$count]['attend_num']=$attend_num_unit;
			$list[$count]['attend_count']=$attend_count_unit;
			$list[$count]['attend_num_school']=$attend_num_school;
			$list[$count]['attend_count_school']=$attend_count_school;
			$list[$count]['avg_school_score']=round(($avg_school_score/$count),2);
			$list[$count]['avg_total_score']=round(($avg_total_score/$count),2);


		}

		// var_dump($list);exit;
		return $list;
	}
	
	//表二 xxx年度课堂教学评价总体情况去听课(听课老师)表统计sql语句获取统计数据
	public function getTable2SQl($params){
		$where=$this->CI->sqlEscape('where status=??',array(AttendStatus::Submit[XPHEnum::Value]));
		$where1=$this->CI->sqlEscape('and status=??',array(AttendStatus::Submit[XPHEnum::Value]));
		if (isset($params['start_time']) && isset($params['end_time'])) {
			$where.=' and attend.listen_time>='.strtotime($params['start_time']).' and attend.listen_time<='.strtotime($params['end_time']);
			$where1.=' and db_attend.listen_time>='.strtotime($params['start_time']).' and db_attend.listen_time<='.strtotime($params['end_time']);
		}

		
		$where.=$this->CI->sqlEscape(' and listen_type=??',array($params['listen_type']));
		$where1.=$this->CI->sqlEscape(' and listen_type=??',array($params['listen_type']));

		// $where.=$this->CI->sqlEscape(' and t.academic_year=??',array(date("Y",strtotime($params['start_time']))));
		// $where1.=$this->CI->sqlEscape(' and t.academic_year=??',array(date("Y",strtotime($params['start_time']))));

		$attendlist = $this->CI->Attend_model -> query("
						SELECT attend.course_units, 
						IFNULL(attend_num,0)attend_num , 
						IFNULL(attend_count,0)attend_count ,
						 IFNULL(attend_num_school,0)attend_num_school,
						 IFNULL(b_attend_num_school,0)b_attend_num_school,
					    IFNULL(attend_count_school,0)attend_count_school, IFNULL(attend_num_outside,0)attend_num_outside,
					    IFNULL(attend_count_outside,0)attend_count_outside, 
					    IFNULL(attend_num_teacher,0)attend_num_teacher, IFNULL(attend_count_teacher,0)attend_count_teacher,
					    IFNULL( attend_num_unitLeader, 0 ) attend_num_unitLeader,IFNULL( attend_count_unitLeader, 0 ) attend_count_unitLeader,
					    IFNULL( attend_num_majorUnitLeader, 0 ) attend_num_majorUnitLeader,
					   	IFNULL( attend_count_majorUnitLeader, 0 ) attend_count_majorUnitLeader,
					   	IFNULL(avg_total_score,0)avg_total_score,
					    IFNULL(avg_school_score,0)avg_school_score,
					    IFNULL(avg_unit_score,0)avg_unit_score,
					    IFNULL(avg_outside_score,0)avg_outside_score,
					    IFNULL(avg_teacher_score,0)avg_teacher_score,
					    IFNULL( avg_unitLeader_score, 0 ) avg_unitLeader_score,
					    IFNULL( avg_majorUnitLeader_score, 0 ) avg_majorUnitLeader_score,
						IFNULL( max90, 0 ) max90,
					    IFNULL( min80, 0 ) min80,
						IFNULL( teacherTotal, 0 ) teacherTotal,
					    concat(max_score,'(',max_teacher,')') max_score,
					    concat(min_score,'(',min_teacher,')') min_score 
					    FROM db_attend attend 
						LEFT JOIN (SELECT count( DISTINCT listen_id ) attend_num,group_concat( DISTINCT `username`  SEPARATOR ',' ) AS username,lt.course_units from db_listen_teacher_info lt  LEFT JOIN db_attend  on db_attend.listen_name = lt.username   WHERE  type = '2' and status=2 $where1 GROUP BY lt.course_units ) attend_num ON attend_num.course_units = attend.course_units 
						LEFT JOIN (SELECT count(*) attend_count,group_concat( DISTINCT `username`  SEPARATOR ',' ) AS username,lt.course_units from db_listen_teacher_info lt  LEFT JOIN db_attend  on db_attend.listen_name = lt.username   WHERE  type = '2' and status=2 $where1 GROUP BY lt.course_units ) attend_count ON attend_count.course_units = attend.course_units 
						LEFT JOIN (SELECT count(DISTINCT teacher_code) b_attend_num_school, course_units FROM db_attend  WHERE  type = '1' and status=2 $where1  GROUP BY course_units ) b_attend_num_school ON b_attend_num_school.course_units = attend.course_units

						LEFT JOIN (SELECT count(DISTINCT listen_id) attend_num_school, course_units FROM db_attend  WHERE  type = '1' and status=2 $where1  GROUP BY course_units ) attend_num_school ON attend_num_school.course_units = attend.course_units
						LEFT JOIN (SELECT count(*) attend_count_school, course_units FROM db_attend  WHERE  type = '1' and status=2 $where1 GROUP BY course_units ) attend_count_school ON attend_count_school.course_units = attend.course_units
						
						LEFT JOIN (SELECT count(DISTINCT listen_id) attend_num_outside, course_units FROM db_attend  WHERE  type = '3' and status=2 $where1 GROUP BY course_units ) attend_num_outside ON attend_num_outside.course_units = attend.course_units
						LEFT JOIN (SELECT count(*) attend_count_outside, course_units FROM db_attend  WHERE  type = '3' and status=2 $where1 GROUP BY course_units ) attend_count_outside ON attend_count_outside.course_units = attend.course_units
						
						LEFT JOIN (SELECT count(DISTINCT listen_id) attend_num_teacher, course_units FROM db_attend  WHERE  type = '4' and status=2 $where1 GROUP BY course_units ) attend_num_teacher ON attend_num_teacher.course_units = attend.course_units 
						LEFT JOIN (SELECT count(*) attend_count_teacher, course_units FROM db_attend  WHERE  type = '4' and status=2 $where1 GROUP BY course_units ) attend_count_teacher ON attend_count_teacher.course_units = attend.course_units 	
						
						LEFT JOIN (SELECT	count( DISTINCT listen_id ) attend_num_unitLeader,	course_units FROM db_attend WHERE type = '5' AND STATUS = 2 AND STATUS = '2' 	$where1 GROUP BY course_units ) attend_num_unitLeader ON attend_num_unitLeader.course_units = attend.course_units LEFT JOIN (SELECT	count(*) attend_count_unitLeader,course_units FROM db_attend WHERE type = '5' AND STATUS = 2 AND STATUS = '2' $where1 GROUP BY	course_units ) attend_count_unitLeader ON attend_count_unitLeader.course_units = attend.course_units

						LEFT JOIN (SELECT	count( DISTINCT listen_id ) attend_num_majorUnitLeader,	course_units FROM db_attend WHERE type = '6' AND STATUS = 2 AND STATUS = '2' $where1 GROUP BY course_units ) attend_num_majorUnitLeader ON attend_num_majorUnitLeader.course_units = attend.course_units
						LEFT JOIN (SELECT	count(*) attend_count_majorUnitLeader,course_units 	FROM db_attend 	WHERE type = '6' AND STATUS = 2 AND STATUS = '2' $where1 GROUP BY course_units  ) attend_count_majorUnitLeader ON attend_count_majorUnitLeader.course_units = attend.course_units
						LEFT JOIN ( SELECT CAST(avg(score) AS DECIMAL(10,2)) avg_total_score, course_units FROM db_attend  WHERE status=2 $where1 GROUP BY course_units ) avg_total_score ON avg_total_score.course_units = attend.course_units
						LEFT JOIN ( SELECT CAST(avg(score) AS DECIMAL(10,2)) avg_school_score, course_units FROM db_attend  WHERE  type='1' and status=2 $where1 GROUP BY course_units ) avg_school_score ON avg_school_score.course_units = attend.course_units 
						LEFT JOIN ( SELECT CAST(avg(score) AS DECIMAL(10,2)) avg_unit_score, course_units FROM db_attend  WHERE  type='2' and status=2 $where1 GROUP BY course_units ) avg_unit_score ON avg_unit_score.course_units = attend.course_units
						LEFT JOIN ( SELECT CAST(avg(score) AS DECIMAL(10,2)) avg_outside_score, course_units FROM db_attend  WHERE  type='3' and status=2 $where1 GROUP BY course_units ) avg_outside_score ON avg_outside_score.course_units = attend.course_units
						LEFT JOIN ( SELECT CAST(avg(score) AS DECIMAL(10,2)) avg_teacher_score, course_units FROM db_attend  WHERE  type='4' and status=2 $where1 GROUP BY course_units ) avg_teacher_score ON avg_teacher_score.course_units = attend.course_units
					    LEFT JOIN ( SELECT	CAST(avg( score ) AS DECIMAL ( 10, 2 )) avg_unitLeader_score,course_units FROM	db_attend WHERE	type = '5' 	AND STATUS = 2 	AND STATUS = '2' $where1 GROUP BY course_units ) avg_unitLeader_score ON avg_unitLeader_score.course_units = attend.course_units
					    LEFT JOIN (	SELECT	CAST(avg( score ) AS DECIMAL ( 10, 2 )) avg_majorUnitLeader_score,course_units FROM	db_attend WHERE	type = '6' 	AND STATUS = 2 	AND STATUS = '2' $where1 GROUP BY course_units ) avg_majorUnitLeader_score ON avg_majorUnitLeader_score.course_units = attend.course_units
					    LEFT JOIN (SELECT max_score, GROUP_CONCAT( DISTINCT teacher_name ) max_teacher, db_attend.course_units FROM ( SELECT max( score ) max_score, course_units FROM db_attend WHERE STATUS = 2 $where1 GROUP BY course_units ) a
					    LEFT JOIN db_attend ON db_attend.score = a.max_score AND db_attend.course_units = a.course_units where status=2 $where1 GROUP BY db_attend.course_units) max_score ON max_score.course_units = attend.course_units
					    LEFT JOIN (SELECT min_score, GROUP_CONCAT( DISTINCT teacher_name ) min_teacher, db_attend.course_units FROM ( SELECT min( score ) min_score, course_units FROM db_attend WHERE STATUS = 2 $where1 GROUP BY course_units ) a 
						LEFT JOIN db_attend ON db_attend.score = a.min_score AND db_attend.course_units = a.course_units where status=2  $where1 GROUP BY db_attend.course_units) min_score ON min_score.course_units = attend.course_units
						LEFT JOIN (SELECT COUNT(DISTINCT teacher_code) as max90,course_units  from db_attend where  type = 1 and `status` = 2 and score >= 90 $where1 GROUP BY course_units ) maxScore on maxScore.course_units = attend.course_units
						LEFT JOIN (SELECT COUNT(DISTINCT teacher_code) as min80,course_units  from db_attend where  type = 1 and `status` = 2 and score < 80 $where1 GROUP BY course_units ) minScore on minScore.course_units = attend.course_units
						LEFT JOIN (SELECT COUNT(DISTINCT usercode) as teacherTotal,course_units from db_teacher_info where academic_year = ".date("Y",strtotime($params['start_time']))." GROUP BY course_units) teacherTotal on teacherTotal.course_units= attend.course_units
						$where GROUP BY attend.course_units  ORDER BY field( dept_name, '商贸学院','机电工程学院', '设计与艺术学院（腾讯云国际互联网学院）', '建筑工程学院', '财经学院',  '交通学院',  '鉴湖学院（纺织学院、黄酒学院、健康学院）', '公共基础教学部（体育部）、马克思主义学院')");
					   
						// LEFT JOIN db_attend ON db_attend.score = a.min_score AND db_attend.course_units = a.course_units where status=2  $where1 GROUP BY db_attend.course_units) min_score ON min_score.course_units = attend.course_units $where GROUP BY attend.course_units  ORDER BY field( dept_name, '商贸分院','电气电子工程分院', '机械工程分院','设计与艺术分院', '建筑工程分院', '财经分院',  '交通分院',  '鉴湖分院', '人文社科部')");
						// echo $this->CI->db->last_query();exit;
						$attend_num_unit=0;
						$attend_count_unit=0;
						$attend_num_school=0;
						$attend_count_school=0;
						$attend_num_outside=0;
						$attend_count_outside=0;
						$attend_num_teacher=0;
						$attend_count_teacher=0;
						$attend_num_unitLeader=0;
						$attend_count_unitLeader=0;
						$attend_num_majorUnitLeader=0;
						$attend_count_majorUnitLeader=0;
						$avg_total_score=0;
						$avg_school_score=0;
						$avg_unit_score=0;
						$avg_outside_score=0;
						$avg_teacher_score=0;
						$avg_unitLeader_score=0;
						$avg_majorUnitLeader_score=0;
						$max90=0;
						$min80=0;
				
				if($attendlist){

				  foreach ($attendlist as $k => $v) {
						$attend_num_unit+=$v['attend_num'];
						$attend_count_unit+=$v['attend_count'];
						$attend_num_school+=$v['attend_num_school'];
						$attend_count_school+=$v['attend_count_school'];
						$attend_num_outside+=$v['attend_num_outside'];
						$attend_count_outside+=$v['attend_count_outside'];
						$attend_num_teacher+=$v['attend_num_teacher'];
						$attend_count_teacher+=$v['attend_count_teacher'];
						$attend_num_unitLeader+=$v['attend_num_unitLeader'];
						$attend_count_unitLeader+=$v['attend_count_unitLeader'];
						$attend_num_majorUnitLeader+=$v['attend_num_majorUnitLeader'];
						$attend_count_majorUnitLeader+=$v['attend_count_majorUnitLeader'];

						$avg_total_score+=$v['avg_total_score'];
						$avg_school_score+=$v['avg_school_score'];
						$avg_unit_score+=$v['avg_unit_score'];
						$avg_outside_score+=$v['avg_outside_score'];
						$avg_teacher_score+=$v['avg_teacher_score'];
						$avg_unitLeader_score+=$v['avg_unitLeader_score'];
						$avg_majorUnitLeader_score+=$v['avg_majorUnitLeader_score'];
						$max90+=$v['max90'];
						$min80+=$v['min80'];
						// $max_count+=$v['max'];
						// $min_count+=$v['min'];
						$attendlist[$k]['is_total'] = 0;
					}

					$count = count($attendlist);

					$attendlist[$count]['course_units']= '合计';
					$attendlist[$count]['attend_num']=$attend_num_unit;
					$attendlist[$count]['attend_count']=$attend_count_unit;
					$attendlist[$count]['attend_num_school']=$attend_num_school;
					$attendlist[$count]['attend_count_school']=$attend_count_school;
					$attendlist[$count]['attend_num_outside']=$attend_num_outside;
					$attendlist[$count]['attend_count_outside']=$attend_count_outside;
					$attendlist[$count]['attend_num_teacher']=$attend_num_teacher;
					$attendlist[$count]['attend_count_teacher']=$attend_count_teacher;
					$attendlist[$count]['attend_num_unitLeader']=$attend_num_unitLeader;
					$attendlist[$count]['attend_count_unitLeader']=$attend_count_unitLeader;
					$attendlist[$count]['attend_num_majorUnitLeader']=$attend_num_majorUnitLeader;
					$attendlist[$count]['attend_count_majorUnitLeader']=$attend_count_majorUnitLeader;
					$attendlist[$count]['avg_total_score']=round(($avg_total_score/$count),2);
					$attendlist[$count]['avg_unit_score']=round(($avg_unit_score/$count),2);
					$attendlist[$count]['avg_school_score']=round(($avg_school_score/$count),2);
					$attendlist[$count]['avg_outside_score']=round(($avg_outside_score/$count),2);
					$attendlist[$count]['avg_teacher_score']=round(($avg_teacher_score/$count),2);
					$attendlist[$count]['avg_unitLeader_score']=round(($avg_unitLeader_score/$count),2);
					$attendlist[$count]['avg_majorUnitLeader_score']=round(($avg_majorUnitLeader_score/$count),2);
					$attendlist[$count]['max_score']='';
					$attendlist[$count]['min_score']='';
					$attendlist[$count]['max90']=$max90;
					$attendlist[$count]['min80']=$min80;
					$attendlist[$count]['is_total'] = 1;
		
						if(count($attendlist)!=0){
							// 教学水平最高分
							$sql=$this->CI->sqlEscape("select * from db_attend where score=(SELECT max(score) from db_attend where status=?? ".$where1." ) and status=?? ".$where1." group by teacher_name",array(AttendStatus::Submit[XPHEnum::Value],AttendStatus::Submit[XPHEnum::Value]));
							$max=$this->CI->Attend_model->query($sql);		
							if($max){
								$max_score=$max[0]['score'];
								$max_teacher_name=implode(',', array_column($max,'teacher_name'));
								$max_level=sprintf("%.2f",$max_score).'('.$max_teacher_name.')';
							}else{
								$max_level= '';
							}	
							
							//教学水平 最低分
							$sql=$this->CI->sqlEscape("select * from db_attend where score=(SELECT min(score) from db_attend where status=?? ".$where1." ) and status=?? ".$where1." group by teacher_name",array(AttendStatus::Submit[XPHEnum::Value],AttendStatus::Submit[XPHEnum::Value]));
							$min=$this->CI->Attend_model->query($sql);			
							if($min){
								$min_score=$min[0]['score'];
								$min_teacher_name=implode(',', array_column($min,'teacher_name'));
								$min_level=sprintf("%.2f",$min_score).'('.$min_teacher_name.')';
							}else{
								$min_level= '';
							}
							
							$attendlist[$count]['max_score']=$max_level;
							$attendlist[$count]['min_score']=$min_level;
						}
				}
			return $attendlist;
	}

	//表三 各二级督导去听课情况
	public function getTableList3($Qualitydetail){
		$table3list = $this->getTable2SQl(array('start_time'=>$Qualitydetail['start_time'],'end_time'=>$Qualitydetail['end_time'],'listen_type'=>$Qualitydetail['listen_type']));
			// var_dump($table2list);
			$table3 = array();
			foreach($table3list as $k=>$v){
				if($k+1 == count($table3list)){
					$table3[$k]['key'] = '合计';
					$table3[$k]['name'] = '';
				}else{
					$table3[$k]['key'] = $k+1;
					$table3[$k]['name'] = $v['course_units'];
				}
				$table3[$k]['value1'] = $v['attend_num'];
				$table3[$k]['value2'] = $v['attend_count'];
				$table3[$k]['value3'] = $v['attend_num'] == 0 ? 0:  round($v['attend_count']/$v['attend_num'],2);
			}

			 $new_b = array();$new_c = array();$new_d = array();
			$str = '';
			foreach($table3 as $k=>$v){
				if($v['key'] !='合计'){
					$new_b[ $v['value2']] =$v['name'];
					$new_d[ $v['value3']] =$v['name'];
					$new_c[$v['name']] = $v;
					if( $v['value3'] < 24 ){
						$str .= ','.$v['name'];
					}
				}else{
					unset($table3[$k]);
				}
			}

			$less_than_24_str = substr($str, 1);

			$attend_count = array_column($table3,'value2');

			array_multisort($attend_count,SORT_DESC,SORT_NUMERIC);//二级督导听课次数排序从大到小
			$attend_count_Arr =  array(
				//二级督导听课次数最多,最少
				'max_attend_name'=>$new_b[$attend_count[0]],//最多的单位
				'avg_attend_value'=>$new_c[$new_b[$attend_count[0]]]['value3'],//最高的单位的全年平均听课次数
				'less_than_24'=>$less_than_24_str,//未操过24次的部门
				'min_attend_name'=>$new_b[$attend_count[count($attend_count)-1]],//听课次数最少的单位
			);
			return array('attend_count_Arr'=>$attend_count_Arr,'new_c'=>$new_c,'table3'=>$table3);
	}

	//表四,课堂教学评价二轮全覆盖完成情况统计表
	public function getTableList4($Qualitydetail){
		$sql = "SELECT group_concat( DISTINCT db_attend.id ) attend_id,db_attend.course_units FROM db_attend  WHERE db_attend.status = '2'  GROUP BY course_units ORDER BY field( course_units, '商贸学院', '机电工程学院', '设计与艺术学院（腾讯云国际互联网学院）','建筑工程学院','财经学院','交通学院','鉴湖学院（纺织学院、黄酒学院、健康学院）','公共基础教学部（体育部）、马克思主义学院' ) ";
		$list = $this->CI->Attend_model->query($sql);
		$str = '';
		$new_a = array();$new_b = array();
		foreach($list as $key=>$value){
			//需听课人数
			$xutingkerenshu = $this->CI->Teacher_info_model->query("SELECT count(usercode) as xtk_num from db_teacher_info where course_units = '".$value['course_units']."' and academic_year = '".date("Y",strtotime($Qualitydetail['start_time']))."'");
			$list[$key]['xtk_num'] = isset($xutingkerenshu[0]['xtk_num'])?$xutingkerenshu[0]['xtk_num']:'0';
			// 被分院督导听课人数 --本分院
			$sql1 ="SELECT count(DISTINCT usercode) as unitsupervisor_num from (SELECT * from db_teacher_info t WHERE t.course_units = '".$value['course_units']."' and t.academic_year = ".date("Y",strtotime($Qualitydetail['start_time'])).") a LEFT JOIN db_attend a on a.teacher_code = a.usercode
			where a.listen_time >= ".strtotime($Qualitydetail['start_time'])."  AND a.listen_time <= ".strtotime($Qualitydetail['end_time'] )." and a.listen_type='".$Qualitydetail['listen_type']."' and a.type = ".AttendType::Unit[XPHEnum::Value]." and a.`status` = ".AttendStatus::Submit[XPHEnum::Value].";";
			// echo $sql1;
			$unitsupervisor_num = $this->CI->Attend_model->query($sql1);
			$list[$key]['unitsupervisor_num'] = isset($unitsupervisor_num[0]['unitsupervisor_num'])?$unitsupervisor_num[0]['unitsupervisor_num']:'0';	

			//未被二级督导听课人数
			$sql3 = "SELECT count(DISTINCT usercode) as wbtk_unitsupervisor_num from db_teacher_info  where course_units = '".$value['course_units']."' and academic_year = ".date("Y",strtotime($Qualitydetail['start_time']))." and usercode not in (SELECT DISTINCT usercode from (SELECT * from db_teacher_info t WHERE t.course_units = '".$value['course_units']."' and t.academic_year = ".date("Y",strtotime($Qualitydetail['start_time'])).") a LEFT JOIN db_attend a on a.teacher_code = a.usercode
			where a.listen_time >= ".strtotime($Qualitydetail['start_time'])."  AND a.listen_time <= ".strtotime($Qualitydetail['end_time'] )." and a.listen_type='".$Qualitydetail['listen_type']."'  and a.type = ".AttendType::Unit[XPHEnum::Value]." and a.`status` = ".AttendStatus::Submit[XPHEnum::Value].") ";
			$wbtk_unitsupervisor_num = $this->CI->Attend_model->query($sql3);
			$list[$key]['wbtk_unitsupervisor_num'] = isset($wbtk_unitsupervisor_num[0]['wbtk_unitsupervisor_num'])?$wbtk_unitsupervisor_num[0]['wbtk_unitsupervisor_num']:'0';
		
			// 被校督导听课人数  	
			$sql4 ="SELECT count(DISTINCT usercode) as schoolsupervisor_num from (SELECT * from db_teacher_info t WHERE t.course_units = '".$value['course_units']."' and t.academic_year = ".date("Y",strtotime($Qualitydetail['start_time'])).") a LEFT JOIN db_attend a on a.teacher_code = a.usercode
			where a.listen_time >= ".strtotime($Qualitydetail['start_time'])."  AND a.listen_time <= ".strtotime($Qualitydetail['end_time'] )."  and a.listen_type='".$Qualitydetail['listen_type']."' and a.type = ".AttendType::School[XPHEnum::Value]." and a.`status` = ".AttendStatus::Submit[XPHEnum::Value].";";
			$schoolsupervisor_num = $this->CI->Attend_model->query($sql4);
			$list[$key]['schoolsupervisor_num'] = isset($schoolsupervisor_num[0]['schoolsupervisor_num'])?$schoolsupervisor_num[0]['schoolsupervisor_num']:'0';		
			
			//未被校督导听课人数
			$sql5 = "SELECT count(DISTINCT usercode) as wbtk_schoolsupervisor_num from db_teacher_info  where course_units = '".$value['course_units']."' and academic_year = ".date("Y",strtotime($Qualitydetail['start_time']))." and usercode not in (SELECT DISTINCT usercode from (SELECT * from db_teacher_info t WHERE t.course_units = '".$value['course_units']."' and t.academic_year = ".date("Y",strtotime($Qualitydetail['start_time'])).") a LEFT JOIN db_attend a on a.teacher_code = a.usercode
			where a.listen_time >= ".strtotime($Qualitydetail['start_time'])."  AND a.listen_time <= ".strtotime($Qualitydetail['end_time'] )." and a.listen_type='".$Qualitydetail['listen_type']."'  and a.type = ".AttendType::School[XPHEnum::Value]." and a.`status` = ".AttendStatus::Submit[XPHEnum::Value].") ";
			$wbtk_schoolsupervisor_num = $this->CI->Attend_model->query($sql5);
			$list[$key]['wbtk_schoolsupervisor_num'] = isset($wbtk_schoolsupervisor_num[0]['wbtk_schoolsupervisor_num'])?$wbtk_schoolsupervisor_num[0]['wbtk_schoolsupervisor_num']:'0';

			//判断都超额完成听课任务的督导分院
			if(isset($wbtk_unitsupervisor_num[0]['wbtk_unitsupervisor_num']) && $wbtk_unitsupervisor_num[0]['wbtk_unitsupervisor_num'] <=0){
				$str .= ",".$value['course_units'];
			}

			$new_a[isset($wbtk_unitsupervisor_num[0]['wbtk_unitsupervisor_num'])?$wbtk_unitsupervisor_num[0]['wbtk_unitsupervisor_num']:'0'] = $value['course_units'];
			$new_b[$value['course_units']] =  isset($xutingkerenshu[0]['xtk_num'])?$xutingkerenshu[0]['xtk_num']:'0';
		}

		$content1 = substr($str, 1)!=''?" <span>".substr($str, 1)."</span> &nbsp; 分院督导均超额完成听课任务":"目前无分院督导超额完成听课任务";
		//未被二级督导听课人数排序
		$wbtk_unitsupervisor_num = array_column($list,'wbtk_unitsupervisor_num');
		array_multisort($wbtk_unitsupervisor_num,SORT_DESC,SORT_NUMERIC);//二级督导听课次数排序从大到小
		// var_dump($wbtk_unitsupervisor_num);
		// var_dump($new_a);

		$contentStr = "从上表可知：大部分二级学院（部）二级督导听课完成情况较好， &nbsp;&nbsp;".$content1."， &nbsp;<span>".$new_a[$wbtk_unitsupervisor_num[0]]."</span> &nbsp;    未被二级督导听课人数较多为  &nbsp;<span>".$wbtk_unitsupervisor_num[0]."</span> &nbsp;  人。校督导听课方面，校督导基本上将各二级学院（部）需听课的教师进行了一轮全覆盖。公共基础教学部（体育部）、马克思主义学院听课   &nbsp;<span>".$new_b['公共基础教学部（体育部）、马克思主义学院']."</span> &nbsp;  人。";

		//统计合计总数
		/* 	$xtk_num=0;
			$unitsupervisor_num=0;
			$wbtk_unitsupervisor_num=0;
			$schoolsupervisor_num=0;
			$wbtk_schoolsupervisor_num=0;
			if($list){
				foreach ($list as $k => $v) {
						$xtk_num+=$v['xtk_num'];
						$unitsupervisor_num+=$v['unitsupervisor_num'];
						$wbtk_unitsupervisor_num+=$v['wbtk_unitsupervisor_num'];
						$schoolsupervisor_num+=$v['schoolsupervisor_num'];
						$wbtk_schoolsupervisor_num+=$v['wbtk_schoolsupervisor_num'];
						$list[$k]['is_total'] = 0;
					}
					$count = count($list);
					$list[$count]['course_units']= '合计';
					$list[$count]['xtk_num']=$xtk_num;
					$list[$count]['unitsupervisor_num']=$unitsupervisor_num;
					$list[$count]['wbtk_unitsupervisor_num']=$wbtk_unitsupervisor_num;
					$list[$count]['schoolsupervisor_num']=$schoolsupervisor_num;
					$list[$count]['wbtk_schoolsupervisor_num']=$wbtk_schoolsupervisor_num;
					$list[$count]['is_total'] = 1;
			} */
			// var_dump($list);

		return array('table4'=>$list,'contentStr'=>$contentStr);
	}

	//表五,校督导课堂教学评价被听课情况统计表
	public function getTableList5($Qualitydetail){
		$params['listen_type'] = $Qualitydetail['listen_type'];
		$table5list = $this->getTable1SQl(array('start_time'=>$Qualitydetail['start_time'],'end_time'=>$Qualitydetail['end_time'],'listen_type'=>$Qualitydetail['listen_type']));
		$table5 = array();
		$course_units = '';$num = 0;$dd = array();
		foreach($table5list as $k=>$v){
			if($v['course_units'] =='合计'){
				unset($table5list[$k]);
			}else{
				$table5[$k]['key'] = $k+1;
				$table5[$k]['name'] = $v['course_units'];
				$table5[$k]['value1'] = $v['attend_num_school'];
				$table5[$k]['value2'] = $v['max90'];
				if($v['max90'] >0){
					$table5[$k]['value3'] = round($v['max90']/$v['attend_num_school']/0.01,2).'%';
				}else{
					$table5[$k]['value3'] = '0%';
				}
				
				if($v['min80'] > 0 ){
					$sql ="SELECT count(DISTINCT teacher_code) tnumber,teachers_attribute from db_attend where listen_time >= ".strtotime($Qualitydetail['start_time'])." and listen_time <= ".strtotime($Qualitydetail['end_time'])." and type = 1 and `status` = 2 and score < 80 and course_units = '".$v['course_units']."'  AND listen_type = '".$params['listen_type']."' GROUP BY teachers_attribute";
					$list = $this->CI->Attend_model->query($sql);
					$str = '';
					foreach($list as $k1=>$v1){
						if(count($list) >1){
							$str .= ','.$v1['tnumber'].$v1['teachers_attribute'];
						}else{
							$str .= ','.$v1['teachers_attribute'];
						}
						$dd[] = $v1['teachers_attribute'];
					}
					$content1 = substr($str, 1);
					
					$table5[$k]['value4'] = $v['min80'].'('.$content1.')';
				}else{
					$table5[$k]['value4'] = $v['min80'];
					$course_units .= ",".$v['course_units'];
				}

				$num += $v['min80'];
			}	
		}
		//分析
		$course_units = substr($course_units, 1);
		$min80 = implode(",",array_unique($dd));
		$miaoshu = "从校督导听课分配中，校级督导评分80分以下的人数为 &nbsp;<span>".$num."</span> &nbsp; 人。其中，&nbsp;<span>".$course_units."</span> &nbsp; 学院的人数为0，其余分院授课教师被评分80分以下者主要为&nbsp;<span>$min80</span> &nbsp;。";
		// var_dump($course_units);
		// var_dump($num); var_dump(array_unique($dd));

		// var_dump($table5);

		return array('table5'=>$table5,'table5miaoshu'=>$miaoshu);
	}

	//各二级学院（部）各类教师听课情况表
	public function getTableList6($Qualitydetail){
		$sql = "SELECT group_concat( DISTINCT db_attend.id ) attend_id,db_attend.course_units FROM db_attend  WHERE db_attend.status = ".AttendStatus::Submit[XPHEnum::Value]."  GROUP BY course_units ORDER BY field( course_units, '商贸学院', '机电工程学院', '设计与艺术学院（腾讯云国际互联网学院）','建筑工程学院','财经学院','交通学院','鉴湖学院（纺织学院、黄酒学院、健康学院）','公共基础教学部（体育部）、马克思主义学院' ) ";
		$list = $this->CI->Attend_model->query($sql);
		$zr_number_t = 0;
		$zr_avg_num_t = 0;
		$jz_number_t = 0;
		$jz_avg_num_t = 0;
		$wp_number_t = 0;
		$wp_avg_num_t = 0;
		$last_num = count($list);
		foreach($list as $key=>$value){
			//查询专任教师,校内兼职教师,,外聘教师听课次数,人均次数,.....
			$sql1 ="SELECT course_units,
				IFNULL( zr_teacher_num, 0 ) zr_teacher_num,
				IFNULL( zr_number, 0 ) zr_number,
				IFNULL( jz_teacher_num, 0 ) jz_teacher_num,
				IFNULL( jz_number, 0 ) jz_number,
				IFNULL( wp_number, 0 ) wp_number ,
				'0' as wp_teacher_num FROM
				( SELECT course_units FROM db_teacher_info WHERE academic_year = ".date("Y",strtotime($Qualitydetail['start_time']))." GROUP BY course_units ) dept_name
				LEFT JOIN (
				SELECT
					count(*) AS zr_teacher_num,
					course_units AS zr_course_units 
				FROM
					db_teacher_info t 
				WHERE
					t.course_units = '".$value['course_units']."' 
					AND t.teacher_type = '专任教师' 
					AND t.academic_year = ".date("Y",strtotime($Qualitydetail['start_time']))." 
				) table1 ON table1.zr_course_units = dept_name.course_units
				LEFT JOIN (
				SELECT
					count(*) AS zr_number,
					selectAll.course_units AS zr_course_units 
				FROM
					( SELECT * FROM db_teacher_info t WHERE t.course_units = '".$value['course_units']."' AND t.teacher_type = '专任教师' AND t.academic_year = ".date("Y",strtotime($Qualitydetail['start_time']))." ) selectAll
					LEFT JOIN db_attend a ON a.teacher_code = selectAll.usercode 
				WHERE
					listen_time >= ".strtotime($Qualitydetail['start_time'])." 
					AND listen_time <= ".strtotime($Qualitydetail['end_time'] )." 
					AND listen_type = '".$Qualitydetail['listen_type']."' 
					AND type = ".AttendType::Unit[XPHEnum::Value]."
					AND `status` = ".AttendStatus::Submit[XPHEnum::Value]."
				) table2 ON table2.zr_course_units = dept_name.course_units
				LEFT JOIN (
				SELECT
					count(*) AS jz_teacher_num,
					course_units AS jz_course_units 
				FROM
					db_teacher_info t 
				WHERE
					t.course_units = '".$value['course_units']."' 
					AND t.teacher_type = '校内兼职教师' 
					AND t.academic_year = ".date("Y",strtotime($Qualitydetail['start_time']))." 
				) table3 ON table3.jz_course_units = dept_name.course_units
				LEFT JOIN (
				SELECT
					count(*) AS jz_number,
					selectAll.course_units AS jz_course_units 
				FROM
					( SELECT * FROM db_teacher_info t WHERE t.course_units = '".$value['course_units']."' AND t.teacher_type = '校内兼职教师' AND t.academic_year = ".date("Y",strtotime($Qualitydetail['start_time']))." ) selectAll
					LEFT JOIN db_attend a ON a.teacher_code = selectAll.usercode 
				WHERE
					listen_time >= ".strtotime($Qualitydetail['start_time'])." 
					AND listen_time <= ".strtotime($Qualitydetail['end_time'] )." 
					AND listen_type = '".$Qualitydetail['listen_type']."' 
					AND type = ".AttendType::Unit[XPHEnum::Value]." 
					AND `status` = ".AttendStatus::Submit[XPHEnum::Value]." 
				) table4 ON table4.jz_course_units = dept_name.course_units
				LEFT JOIN (
				SELECT
					count(*) AS wp_number,
					wp_course_units 
				FROM
					(
					SELECT
						att.id,
						listen_id,
						att.course_units AS wp_course_units,
						att.teachers_attribute,
						listen_type,
						a.usercode,
						a.sub_role_id 
					FROM
						db_attend att
						LEFT JOIN db_admin a ON a.username = att.teacher_name 
						AND a.`status` = ".CommonStatus::UnDeleted[XPHEnum::Value]." 
					WHERE
						listen_time >= ".strtotime($Qualitydetail['start_time'])." 
						AND listen_time <= ".strtotime($Qualitydetail['end_time'] )." 
						AND listen_type = '".$Qualitydetail['listen_type']."' 
						AND type = ".AttendType::Unit[XPHEnum::Value]." 
						AND att.`status` = ".AttendStatus::Submit[XPHEnum::Value]." 
						AND (att.teachers_attribute = '外聘教师' or (a.role_id =  ".WebRole::ExternalSupervisor[XPHEnum::Value]."  or FIND_IN_SET( ".WebRole::ExternalSupervisor[XPHEnum::Value]." ,a.sub_role_id)))
						AND att.course_units = '".$value['course_units']."' 
					GROUP BY
						teacher_name 
					) table5 
				) table55 ON table55.wp_course_units = dept_name.course_units 
				WHERE
				dept_name.course_units = '".$value['course_units']."'";
				// echo $sql1;
			$rest = $this->CI->Teacher_info_model->query($sql1);
			// exit;
			//专任教师被听课次数
			$zr_number= isset($rest[0]['zr_number'])?$rest[0]['zr_number']:'0';
			$list[$key]['zr_number'] = $zr_number;
			$zr_number_t += $zr_number;
			//专任教师人数
			$zr_teacher_num = isset($rest[0]['zr_teacher_num'])?$rest[0]['zr_teacher_num']:'0';
			$list[$key]['zr_teacher_num'] = $zr_teacher_num;
			//专任教师人均被听课次数(专任教师被听课次数/专任教师人数)
			$list[$key]['zr_avg_num'] = $zr_teacher_num >0?round($zr_number/$zr_teacher_num,2) :'0';
			$zr_avg_num_t += $list[$key]['zr_avg_num'];
			//校内兼职教师被听课次数
			$jz_number= isset($rest[0]['jz_number'])?$rest[0]['jz_number']:'0';
			$list[$key]['jz_number'] = $jz_number;
			$jz_number_t += $jz_number;
			//校内兼职教师人数
			$jz_teacher_num = isset($rest[0]['jz_teacher_num'])?$rest[0]['jz_teacher_num']:'0';
			$list[$key]['jz_teacher_num'] = $jz_teacher_num;
			//校内兼职教师被听课次数
			$list[$key]['jz_avg_num'] = $jz_teacher_num >0?round($jz_number/$jz_teacher_num,2) :'0';
			$jz_avg_num_t += $list[$key]['jz_avg_num'];

			//外聘教师被听课次数
			$wp_number = isset($rest[0]['wp_number'])?$rest[0]['wp_number']:'0';
			$list[$key]['wp_number'] = $wp_number;
			$wp_number_t += $wp_number;
			//外聘教师人数
			$wp_teacher_num = isset($rest[0]['wp_teacher_num'])?$rest[0]['wp_teacher_num']:'0';
			$list[$key]['wp_teacher_num'] = $wp_teacher_num;
			//外聘教师人均被听课次数
			$list[$key]['wp_avg_num'] = $wp_teacher_num >0?round($wp_number/$wp_teacher_num,2) :'0';
			$wp_avg_num_t += $list[$key]['wp_avg_num'];
		}

		$list[$last_num]['course_units'] ='合计';
		$list[$last_num]['zr_number'] =$zr_number_t;
		$list[$last_num]['zr_avg_num'] =$zr_avg_num_t;
		$list[$last_num]['jz_number'] =$jz_number_t;
		$list[$last_num]['jz_avg_num'] =$jz_avg_num_t;
		$list[$last_num]['wp_number'] =$wp_number_t;
		$list[$last_num]['wp_avg_num'] =$wp_avg_num_t;

		// var_dump($list);

		return array('table6'=>$list);
	}

	//附件1
	public function fujian1($Qualitydetail){
		$start_time = strtotime($Qualitydetail['start_month']);
		$end_time = strtotime($Qualitydetail['end_month']);
		$year = date("Y",strtotime($Qualitydetail['start_month']));
		$listen_type = $Qualitydetail['listen_type'];
		// $sql="select `t`.`id` AS `id`,`t`.`usercode` AS `usercode`,`t`.`username` AS `username`,`t`.`academic_year` AS `academic_year`,`t`.`course_units` AS `course_units`,`atterd2_1`.`listen_score1` AS `listen_score1`,`atterd2_2`.`listen_score2` AS `listen_score2`,`atterd2_3`.`listen_score3` AS `listen_score3`,`atterd2_4`.`listen_score4` AS `listen_score4`,`atterd2_5`.`listen_score5` AS `listen_score5`,`atterd2_6`.`listen_score6` AS `listen_score6`,`atterd2_7`.`listen_score7` AS `listen_score7`,`atterd1`.`listen_score8` AS `listen_score8`,`atterd1_1`.`listen_score9` AS `listen_score9` from (((((((((`zjipc_supervision`.`db_teacher_info` `t` left join (select `zjipc_supervision`.`db_attend`.`teacher_code` AS `teacher_code`,`zjipc_supervision`.`db_attend`.`teacher_name` AS `teacher_name`,`zjipc_supervision`.`db_attend`.`listen_name` AS `listen_name`,group_concat(`zjipc_supervision`.`db_attend`.`score` order by field(`zjipc_supervision`.`db_attend`.`listen_name`,'冯根尧','其他') ASC separator ',') AS `listen_score8` from `zjipc_supervision`.`db_attend` where ((`zjipc_supervision`.`db_attend`.`status` = 2) and (`zjipc_supervision`.`db_attend`.`type` = 1) and (`zjipc_supervision`.`db_attend`.`listen_time` >= ".$start_time.") and (`zjipc_supervision`.`db_attend`.`listen_time` <= ".$end_time.") and (`zjipc_supervision`.`db_attend`.`listen_name` = '冯根尧') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '暂无工号') and (`zjipc_supervision`.`db_attend`.`listen_type` = '日常听课')) group by `zjipc_supervision`.`db_attend`.`teacher_code`) `atterd1` on(((`atterd1`.`teacher_code` = `t`.`usercode`) and (`atterd1`.`teacher_name` = `t`.`username`)))) left join (select `zjipc_supervision`.`db_attend`.`teacher_code` AS `teacher_code`,`zjipc_supervision`.`db_attend`.`teacher_name` AS `teacher_name`,`zjipc_supervision`.`db_attend`.`listen_name` AS `listen_name`,group_concat(`zjipc_supervision`.`db_attend`.`score` order by field(`zjipc_supervision`.`db_attend`.`listen_name`,'其他') ASC separator ',') AS `listen_score9` from `zjipc_supervision`.`db_attend` where ((`zjipc_supervision`.`db_attend`.`status` = 2) and (`zjipc_supervision`.`db_attend`.`type` = 1) and (`zjipc_supervision`.`db_attend`.`listen_time` >= ".$start_time.") and (`zjipc_supervision`.`db_attend`.`listen_time` <= ".$end_time.") and (`zjipc_supervision`.`db_attend`.`listen_name` <> '冯根尧') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '暂无工号') and (`zjipc_supervision`.`db_attend`.`listen_type` = '日常听课')) group by `zjipc_supervision`.`db_attend`.`teacher_code`) `atterd1_1` on(((`atterd1_1`.`teacher_code` = `t`.`usercode`) and (`atterd1_1`.`teacher_name` = `t`.`username`)))) left join (select `zjipc_supervision`.`db_attend`.`teacher_code` AS `teacher_code`,`zjipc_supervision`.`db_attend`.`teacher_name` AS `teacher_name`,`zjipc_supervision`.`db_attend`.`listen_name` AS `listen_name`,group_concat(`zjipc_supervision`.`db_attend`.`score` order by field(`zjipc_supervision`.`db_attend`.`listen_name`,'胡敏','其他') ASC separator ',') AS `listen_score1` from `zjipc_supervision`.`db_attend` where ((`zjipc_supervision`.`db_attend`.`status` = 2) and (`zjipc_supervision`.`db_attend`.`type` = 2) and (`zjipc_supervision`.`db_attend`.`listen_time` >= ".$start_time.") and (`zjipc_supervision`.`db_attend`.`listen_time` <= ".$end_time.") and (`zjipc_supervision`.`db_attend`.`listen_name` = '胡敏') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '暂无工号') and (`zjipc_supervision`.`db_attend`.`listen_type` = '日常听课')) group by `zjipc_supervision`.`db_attend`.`teacher_code`) `atterd2_1` on(((`atterd2_1`.`teacher_code` = `t`.`usercode`) and (`atterd2_1`.`teacher_name` = `t`.`username`)))) left join (select `zjipc_supervision`.`db_attend`.`teacher_code` AS `teacher_code`,`zjipc_supervision`.`db_attend`.`teacher_name` AS `teacher_name`,`zjipc_supervision`.`db_attend`.`listen_name` AS `listen_name`,group_concat(`zjipc_supervision`.`db_attend`.`score` order by field(`zjipc_supervision`.`db_attend`.`listen_name`,'高龙士','其他') ASC separator ',') AS `listen_score2` from `zjipc_supervision`.`db_attend` where ((`zjipc_supervision`.`db_attend`.`status` = 2) and (`zjipc_supervision`.`db_attend`.`type` = 2) and (`zjipc_supervision`.`db_attend`.`listen_time` >= ".$start_time.") and (`zjipc_supervision`.`db_attend`.`listen_time` <= ".$end_time.") and (`zjipc_supervision`.`db_attend`.`listen_name` = '高龙士') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '暂无工号') and (`zjipc_supervision`.`db_attend`.`listen_type` = '日常听课')) group by `zjipc_supervision`.`db_attend`.`teacher_code`) `atterd2_2` on(((`atterd2_2`.`teacher_code` = `t`.`usercode`) and (`atterd2_2`.`teacher_name` = `t`.`username`)))) left join (select `zjipc_supervision`.`db_attend`.`teacher_code` AS `teacher_code`,`zjipc_supervision`.`db_attend`.`teacher_name` AS `teacher_name`,`zjipc_supervision`.`db_attend`.`listen_name` AS `listen_name`,group_concat(`zjipc_supervision`.`db_attend`.`score` order by field(`zjipc_supervision`.`db_attend`.`listen_name`,'徐君燕','其他') ASC separator ',') AS `listen_score3` from `zjipc_supervision`.`db_attend` where ((`zjipc_supervision`.`db_attend`.`status` = 2) and (`zjipc_supervision`.`db_attend`.`type` = 2) and (`zjipc_supervision`.`db_attend`.`listen_time` >= ".$start_time.") and (`zjipc_supervision`.`db_attend`.`listen_time` <= ".$end_time.") and (`zjipc_supervision`.`db_attend`.`listen_name` = '徐君燕') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '暂无工号') and (`zjipc_supervision`.`db_attend`.`listen_type` = '日常听课')) group by `zjipc_supervision`.`db_attend`.`teacher_code`) `atterd2_3` on(((`atterd2_3`.`teacher_code` = `t`.`usercode`) and (`atterd2_3`.`teacher_name` = `t`.`username`)))) left join (select `zjipc_supervision`.`db_attend`.`teacher_code` AS `teacher_code`,`zjipc_supervision`.`db_attend`.`teacher_name` AS `teacher_name`,`zjipc_supervision`.`db_attend`.`listen_name` AS `listen_name`,group_concat(`zjipc_supervision`.`db_attend`.`score` order by field(`zjipc_supervision`.`db_attend`.`listen_name`,'陈怀忠','其他') ASC separator ',') AS `listen_score4` from `zjipc_supervision`.`db_attend` where ((`zjipc_supervision`.`db_attend`.`status` = 2) and (`zjipc_supervision`.`db_attend`.`type` = 2) and (`zjipc_supervision`.`db_attend`.`listen_time` >= ".$start_time.") and (`zjipc_supervision`.`db_attend`.`listen_time` <= ".$end_time.") and (`zjipc_supervision`.`db_attend`.`listen_name` = '陈怀忠') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '暂无工号') and (`zjipc_supervision`.`db_attend`.`listen_type` = '日常听课')) group by `zjipc_supervision`.`db_attend`.`teacher_code`) `atterd2_4` on(((`atterd2_4`.`teacher_code` = `t`.`usercode`) and (`atterd2_4`.`teacher_name` = `t`.`username`)))) left join (select `zjipc_supervision`.`db_attend`.`teacher_code` AS `teacher_code`,`zjipc_supervision`.`db_attend`.`teacher_name` AS `teacher_name`,`zjipc_supervision`.`db_attend`.`listen_name` AS `listen_name`,group_concat(`zjipc_supervision`.`db_attend`.`score` order by field(`zjipc_supervision`.`db_attend`.`listen_name`,'叶海见','其他') ASC separator ',') AS `listen_score5` from `zjipc_supervision`.`db_attend` where ((`zjipc_supervision`.`db_attend`.`status` = 2) and (`zjipc_supervision`.`db_attend`.`type` = 2) and (`zjipc_supervision`.`db_attend`.`listen_time` >= ".$start_time.") and (`zjipc_supervision`.`db_attend`.`listen_time` <= ".$end_time.") and (`zjipc_supervision`.`db_attend`.`listen_name` = '叶海见') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '暂无工号') and (`zjipc_supervision`.`db_attend`.`listen_type` = '日常听课')) group by `zjipc_supervision`.`db_attend`.`teacher_code`) `atterd2_5` on(((`atterd2_5`.`teacher_code` = `t`.`usercode`) and (`atterd2_5`.`teacher_name` = `t`.`username`)))) left join (select `zjipc_supervision`.`db_attend`.`teacher_code` AS `teacher_code`,`zjipc_supervision`.`db_attend`.`teacher_name` AS `teacher_name`,`zjipc_supervision`.`db_attend`.`listen_name` AS `listen_name`,group_concat(`zjipc_supervision`.`db_attend`.`score` order by field(`zjipc_supervision`.`db_attend`.`listen_name`,'黄烨','其他') ASC separator ',') AS `listen_score6` from `zjipc_supervision`.`db_attend` where ((`zjipc_supervision`.`db_attend`.`status` = 2) and (`zjipc_supervision`.`db_attend`.`type` = 2) and (`zjipc_supervision`.`db_attend`.`listen_time` >= ".$start_time.") and (`zjipc_supervision`.`db_attend`.`listen_time` <= ".$end_time.") and (`zjipc_supervision`.`db_attend`.`listen_name` = '黄烨') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '暂无工号') and (`zjipc_supervision`.`db_attend`.`listen_type` = '日常听课')) group by `zjipc_supervision`.`db_attend`.`teacher_code`) `atterd2_6` on(((`atterd2_6`.`teacher_code` = `t`.`usercode`) and (`atterd2_6`.`teacher_name` = `t`.`username`)))) left join (select `zjipc_supervision`.`db_attend`.`teacher_code` AS `teacher_code`,`zjipc_supervision`.`db_attend`.`teacher_name` AS `teacher_name`,`zjipc_supervision`.`db_attend`.`listen_name` AS `listen_name`,group_concat(`zjipc_supervision`.`db_attend`.`score` order by field(`zjipc_supervision`.`db_attend`.`listen_name`,'其他') ASC separator ',') AS `listen_score7` from `zjipc_supervision`.`db_attend` where ((`zjipc_supervision`.`db_attend`.`status` = 2) and (`zjipc_supervision`.`db_attend`.`type` = 2) and (`zjipc_supervision`.`db_attend`.`listen_time` >= ".$start_time.") and (`zjipc_supervision`.`db_attend`.`listen_time` <= ".$end_time.") and (`zjipc_supervision`.`db_attend`.`listen_name` not in ('胡敏','高龙士','徐君燕','陈怀忠','叶海见','黄烨')) and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '暂无工号') and (`zjipc_supervision`.`db_attend`.`listen_type` = '日常听课')) group by `zjipc_supervision`.`db_attend`.`teacher_code`) `atterd2_7` on(((`atterd2_7`.`teacher_code` = `t`.`usercode`) and (`atterd2_7`.`teacher_name` = `t`.`username`)))) where ((`t`.`academic_year` = ".$year.") and (`t`.`course_units` = '机电工程学院')) order by `t`.`id`";
		$dudaolist = array('二级-胡敏','二级-高龙士','二级-徐君燕','二级-陈怀忠','二级-叶海见','二级-黄烨','二级-其他','校-冯根尧','校-其他');
		$erjidudao ="'胡敏','高龙士','徐君燕','陈怀忠','黄烨'";
		$xiaodudao ="'冯根尧'";
		$danwei = '机电工程学院';
		$list = $this->getDuDaoScoreSql($start_time,$end_time,$year,$dudaolist,$erjidudao,$xiaodudao,$danwei,$listen_type);
		/*  */
		if($list){
			foreach($list as $k=>$v){
				$a = array();
				if($v['listen_score1']){
				$a[] = explode(",",$v['listen_score1']);
				}
				if($v['listen_score2']){
				$a[] = explode(",",$v['listen_score2']);
				}
				if($v['listen_score3']){
					$a[] = explode(",",$v['listen_score3']);
				}
				if($v['listen_score4']){
					$a[] = explode(",",$v['listen_score4']);
				}
				if($v['listen_score5']){
					$a[] = explode(",",$v['listen_score5']);
				}
				if($v['listen_score6']){
					$a[] = explode(",",$v['listen_score6']);
				}
				if($v['listen_score7']){
					$a[] = explode(",",$v['listen_score7']);
				}
				if($v['listen_score8']){
					$a[] = explode(",",$v['listen_score8']);
				}
				if($v['listen_score9']){
					$a[] = explode(",",$v['listen_score9']);
				}

				$new_a = arrayTwo_One($a);//将二维数组转为一维数组导出
		
				if($new_a){
					$avg = round(array_sum($new_a)/count($new_a),2) ;
				}else{
					$avg = '';
				}
				$list[$k]['key'] = $k+1;
				$list[$k]['listen_score1'] = $v['listen_score1']?$v['listen_score1']:'';//督导评分1
				$list[$k]['listen_score2'] = $v['listen_score2']?$v['listen_score2']:'';//督导评分2
				$list[$k]['listen_score3'] = $v['listen_score3']?$v['listen_score3']:'';//督导评分3
				$list[$k]['listen_score4'] = $v['listen_score4']?$v['listen_score4']:'';//督导评分4
				$list[$k]['listen_score5'] = $v['listen_score5']?$v['listen_score5']:'';//督导评分5
				$list[$k]['listen_score6'] = $v['listen_score6']?$v['listen_score6']:'';//督导评分6
				$list[$k]['listen_score7'] = $v['listen_score7']?$v['listen_score7']:'';//督导评分7
				$list[$k]['listen_score8'] = $v['listen_score8']?$v['listen_score8']:'';//督导评分8
				$list[$k]['listen_score9'] = $v['listen_score9']?$v['listen_score9']:'';//督导评分9
				$list[$k]['listen_score10'] = $avg;//总平均分
			}
		}
		// var_dump($list);
		return $list;
	}
	//附件2
	public function fujian2($Qualitydetail){
		$start_time = strtotime($Qualitydetail['start_month']);
		$end_time = strtotime($Qualitydetail['end_month']);
		$year = date("Y",strtotime($Qualitydetail['start_month']));
		$listen_type = $Qualitydetail['listen_type'];
		// $sql = "select `t`.`id` AS `id`,`t`.`usercode` AS `usercode`,`t`.`username` AS `username`,`t`.`academic_year` AS `academic_year`,`t`.`course_units` AS `course_units`,`atterd2_1`.`listen_score1` AS `listen_score1`,`atterd2_2`.`listen_score2` AS `listen_score2`,`atterd2_3`.`listen_score3` AS `listen_score3`,`atterd1`.`listen_score4` AS `listen_score4` from ((((`zjipc_supervision`.`db_teacher_info` `t` left join (select `zjipc_supervision`.`db_attend`.`teacher_code` AS `teacher_code`,`zjipc_supervision`.`db_attend`.`teacher_name` AS `teacher_name`,`zjipc_supervision`.`db_attend`.`listen_name` AS `listen_name`,group_concat(`zjipc_supervision`.`db_attend`.`score` order by field(`zjipc_supervision`.`db_attend`.`listen_name`,'张凤珍','其他') ASC separator ',') AS `listen_score4` from `zjipc_supervision`.`db_attend` where ((`zjipc_supervision`.`db_attend`.`status` = 2) and (`zjipc_supervision`.`db_attend`.`type` = 1) and (`zjipc_supervision`.`db_attend`.`listen_time` >= ".$start_time.") and (`zjipc_supervision`.`db_attend`.`listen_time` <= ".$end_time.") and (`zjipc_supervision`.`db_attend`.`listen_name` = '张凤珍') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '暂无工号') and (`zjipc_supervision`.`db_attend`.`listen_type` = '日常听课')) group by `zjipc_supervision`.`db_attend`.`teacher_code`) `atterd1` on(((`atterd1`.`teacher_code` = `t`.`usercode`) and (`atterd1`.`teacher_name` = `t`.`username`)))) left join (select `zjipc_supervision`.`db_attend`.`teacher_code` AS `teacher_code`,`zjipc_supervision`.`db_attend`.`teacher_name` AS `teacher_name`,`zjipc_supervision`.`db_attend`.`listen_name` AS `listen_name`,group_concat(`zjipc_supervision`.`db_attend`.`score` order by field(`zjipc_supervision`.`db_attend`.`listen_name`,'宿敏','其他') ASC separator ',') AS `listen_score1` from `zjipc_supervision`.`db_attend` where ((`zjipc_supervision`.`db_attend`.`status` = 2) and (`zjipc_supervision`.`db_attend`.`type` = 2) and (`zjipc_supervision`.`db_attend`.`listen_time` >= ".$start_time.") and (`zjipc_supervision`.`db_attend`.`listen_time` <= ".$end_time.") and (`zjipc_supervision`.`db_attend`.`listen_name` = '宿敏') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '暂无工号') and (`zjipc_supervision`.`db_attend`.`listen_type` = '日常听课')) group by `zjipc_supervision`.`db_attend`.`teacher_code`) `atterd2_1` on(((`atterd2_1`.`teacher_code` = `t`.`usercode`) and (`atterd2_1`.`teacher_name` = `t`.`username`)))) left join (select `zjipc_supervision`.`db_attend`.`teacher_code` AS `teacher_code`,`zjipc_supervision`.`db_attend`.`teacher_name` AS `teacher_name`,`zjipc_supervision`.`db_attend`.`listen_name` AS `listen_name`,group_concat(`zjipc_supervision`.`db_attend`.`score` order by field(`zjipc_supervision`.`db_attend`.`listen_name`,'翁窈瑶','其他') ASC separator ',') AS `listen_score2` from `zjipc_supervision`.`db_attend` where ((`zjipc_supervision`.`db_attend`.`status` = 2) and (`zjipc_supervision`.`db_attend`.`type` = 2) and (`zjipc_supervision`.`db_attend`.`listen_time` >= ".$start_time.") and (`zjipc_supervision`.`db_attend`.`listen_time` <= ".$end_time.") and (`zjipc_supervision`.`db_attend`.`listen_name` = '翁窈瑶') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '暂无工号') and (`zjipc_supervision`.`db_attend`.`listen_type` = '日常听课')) group by `zjipc_supervision`.`db_attend`.`teacher_code`) `atterd2_2` on(((`atterd2_2`.`teacher_code` = `t`.`usercode`) and (`atterd2_2`.`teacher_name` = `t`.`username`)))) left join (select `zjipc_supervision`.`db_attend`.`teacher_code` AS `teacher_code`,`zjipc_supervision`.`db_attend`.`teacher_name` AS `teacher_name`,`zjipc_supervision`.`db_attend`.`listen_name` AS `listen_name`,group_concat(`zjipc_supervision`.`db_attend`.`score` order by field(`zjipc_supervision`.`db_attend`.`listen_name`,'曾焱','其他') ASC separator ',') AS `listen_score3` from `zjipc_supervision`.`db_attend` where ((`zjipc_supervision`.`db_attend`.`status` = 2) and (`zjipc_supervision`.`db_attend`.`type` = 2) and (`zjipc_supervision`.`db_attend`.`listen_time` >= ".$start_time.") and (`zjipc_supervision`.`db_attend`.`listen_time` <= ".$end_time.") and (`zjipc_supervision`.`db_attend`.`listen_name` = '曾焱') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '暂无工号') and (`zjipc_supervision`.`db_attend`.`listen_type` = '日常听课')) group by `zjipc_supervision`.`db_attend`.`teacher_code`) `atterd2_3` on(((`atterd2_3`.`teacher_code` = `t`.`usercode`) and (`atterd2_3`.`teacher_name` = `t`.`username`)))) where ((`t`.`academic_year` = ".$year.") and (`t`.`course_units` = '建筑工程学院')) order by `t`.`id`";
		// $list = $this->CI->Teacher_info_model->query($sql);
		$dudaolist = array('二级-宿敏','二级-翁窈瑶','二级-曾焱','校-张凤珍');
		$erjidudao ="'胡敏','翁窈瑶','曾焱'";
		$xiaodudao ="'张凤珍'";
		$danwei = '建筑工程学院';
		$list = $this->getDuDaoScoreSql($start_time,$end_time,$year,$dudaolist,$erjidudao,$xiaodudao,$danwei,$listen_type);

		if($list){
			foreach($list as $k=>$v){
				$a = array();
				if($v['listen_score1']){
				  $a[] = explode(",",$v['listen_score1']);
				}
				if($v['listen_score2']){
				  $a[] = explode(",",$v['listen_score2']);
				}
				if($v['listen_score3']){
					$a[] = explode(",",$v['listen_score3']);
				}
				if($v['listen_score4']){
					$a[] = explode(",",$v['listen_score4']);
				}
	
				$new_a = arrayTwo_One($a);//将二维数组转为一维数组导出
		
				if($new_a){
					$avg = round(array_sum($new_a)/count($new_a),2) ;
				}else{
					$avg = '';
				}
				$list[$k]['key'] = $k+1;
				$list[$k]['listen_score1'] = $v['listen_score1']?$v['listen_score1']:'';//督导评分1
				$list[$k]['listen_score2'] = $v['listen_score2']?$v['listen_score2']:'';//督导评分2
				$list[$k]['listen_score3'] = $v['listen_score3']?$v['listen_score3']:'';//督导评分3
				$list[$k]['listen_score4'] = $v['listen_score4']?$v['listen_score4']:'';//督导评分4
				$list[$k]['listen_score5'] = $avg;//督导评分5
			}
		}
		
		return $list;
	}

	//附件3财经学院督导评分
	public function fujian3($Qualitydetail){
		$start_time = strtotime($Qualitydetail['start_month']);
		$end_time = strtotime($Qualitydetail['end_month']);
		$year = date("Y",strtotime($Qualitydetail['start_month']));
		$listen_type = $Qualitydetail['listen_type'];
		// $sql ="select `t`.`id` AS `id`,`t`.`usercode` AS `usercode`,`t`.`username` AS `username`,`t`.`academic_year` AS `academic_year`,`t`.`course_units` AS `course_units`,`atterd2_1`.`listen_score1` AS `listen_score1`,`atterd2_2`.`listen_score2` AS `listen_score2`,`atterd2_3`.`listen_score3` AS `listen_score3`,`atterd1`.`listen_score4` AS `listen_score4` from ((((`zjipc_supervision`.`db_teacher_info` `t` left join (select `zjipc_supervision`.`db_attend`.`teacher_code` AS `teacher_code`,`zjipc_supervision`.`db_attend`.`teacher_name` AS `teacher_name`,`zjipc_supervision`.`db_attend`.`listen_name` AS `listen_name`,group_concat(`zjipc_supervision`.`db_attend`.`score` order by field(`zjipc_supervision`.`db_attend`.`listen_name`,'张凤珍','其他') ASC separator ',') AS `listen_score4` from `zjipc_supervision`.`db_attend` where ((`zjipc_supervision`.`db_attend`.`status` = 2) and (`zjipc_supervision`.`db_attend`.`type` = 1) and (`zjipc_supervision`.`db_attend`.`listen_time` >= ".$start_time.") and (`zjipc_supervision`.`db_attend`.`listen_time` <= ".$end_time.") and (`zjipc_supervision`.`db_attend`.`listen_name` = '张凤珍') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '暂无工号') and (`zjipc_supervision`.`db_attend`.`listen_type` = '日常听课')) group by `zjipc_supervision`.`db_attend`.`teacher_code`) `atterd1` on(((`atterd1`.`teacher_code` = `t`.`usercode`) and (`atterd1`.`teacher_name` = `t`.`username`)))) left join (select `zjipc_supervision`.`db_attend`.`teacher_code` AS `teacher_code`,`zjipc_supervision`.`db_attend`.`teacher_name` AS `teacher_name`,`zjipc_supervision`.`db_attend`.`listen_name` AS `listen_name`,group_concat(`zjipc_supervision`.`db_attend`.`score` order by field(`zjipc_supervision`.`db_attend`.`listen_name`,'祁舒慧','其他') ASC separator ',') AS `listen_score1` from `zjipc_supervision`.`db_attend` where ((`zjipc_supervision`.`db_attend`.`status` = 2) and (`zjipc_supervision`.`db_attend`.`type` = 2) and (`zjipc_supervision`.`db_attend`.`listen_time` >= ".$start_time.") and (`zjipc_supervision`.`db_attend`.`listen_time` <= ".$end_time.") and (`zjipc_supervision`.`db_attend`.`listen_name` = '祁舒慧') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '暂无工号') and (`zjipc_supervision`.`db_attend`.`listen_type` = '日常听课')) group by `zjipc_supervision`.`db_attend`.`teacher_code`) `atterd2_1` on(((`atterd2_1`.`teacher_code` = `t`.`usercode`) and (`atterd2_1`.`teacher_name` = `t`.`username`)))) left join (select `zjipc_supervision`.`db_attend`.`teacher_code` AS `teacher_code`,`zjipc_supervision`.`db_attend`.`teacher_name` AS `teacher_name`,`zjipc_supervision`.`db_attend`.`listen_name` AS `listen_name`,group_concat(`zjipc_supervision`.`db_attend`.`score` order by field(`zjipc_supervision`.`db_attend`.`listen_name`,'赵威','其他') ASC separator ',') AS `listen_score2` from `zjipc_supervision`.`db_attend` where ((`zjipc_supervision`.`db_attend`.`status` = 2) and (`zjipc_supervision`.`db_attend`.`type` = 2) and (`zjipc_supervision`.`db_attend`.`listen_time` >= ".$start_time.") and (`zjipc_supervision`.`db_attend`.`listen_time` <= ".$end_time.") and (`zjipc_supervision`.`db_attend`.`listen_name` = '赵威') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '暂无工号') and (`zjipc_supervision`.`db_attend`.`listen_type` = '日常听课')) group by `zjipc_supervision`.`db_attend`.`teacher_code`) `atterd2_2` on(((`atterd2_2`.`teacher_code` = `t`.`usercode`) and (`atterd2_2`.`teacher_name` = `t`.`username`)))) left join (select `zjipc_supervision`.`db_attend`.`teacher_code` AS `teacher_code`,`zjipc_supervision`.`db_attend`.`teacher_name` AS `teacher_name`,`zjipc_supervision`.`db_attend`.`listen_name` AS `listen_name`,group_concat(`zjipc_supervision`.`db_attend`.`score` order by field(`zjipc_supervision`.`db_attend`.`listen_name`,'赵威','其他') ASC separator ',') AS `listen_score3` from `zjipc_supervision`.`db_attend` where ((`zjipc_supervision`.`db_attend`.`status` = 2) and (`zjipc_supervision`.`db_attend`.`type` = 2) and (`zjipc_supervision`.`db_attend`.`listen_time` >= ".$start_time.") and (`zjipc_supervision`.`db_attend`.`listen_time` <= ".$end_time.") and (`zjipc_supervision`.`db_attend`.`listen_name` = '张洲平') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '暂无工号') and (`zjipc_supervision`.`db_attend`.`listen_type` = '日常听课')) group by `zjipc_supervision`.`db_attend`.`teacher_code`) `atterd2_3` on(((`atterd2_3`.`teacher_code` = `t`.`usercode`) and (`atterd2_3`.`teacher_name` = `t`.`username`)))) where ((`t`.`academic_year` = ".$year.") and (`t`.`course_units` = '财经学院')) order by `t`.`id`";
		// $list = $this->CI->Teacher_info_model->query($sql);
		$dudaolist = array('二级-祁舒慧','二级-赵威','二级-张洲平','校-张凤珍');
		$erjidudao ="'祁舒慧','赵威','张洲平'";
		$xiaodudao ="'张凤珍'";
		$danwei = '财经学院';
		$list = $this->getDuDaoScoreSql($start_time,$end_time,$year,$dudaolist,$erjidudao,$xiaodudao,$danwei,$listen_type);
		if($list){
			foreach($list as $k=>$v){
				$a = array();
				if($v['listen_score1']){
				$a[] = explode(",",$v['listen_score1']);
				}
				if($v['listen_score2']){
				$a[] = explode(",",$v['listen_score2']);
				}
				if($v['listen_score3']){
					$a[] = explode(",",$v['listen_score3']);
				}
				if($v['listen_score4']){
					$a[] = explode(",",$v['listen_score4']);
				}
				$new_a = arrayTwo_One($a);//将二维数组转为一维数组导出

				if($new_a){
					$avg = round(array_sum($new_a)/count($new_a),2) ;
				}else{
					$avg = '';
				}
				$list[$k]['key'] = $k+1;
				$list[$k]['listen_score1'] = $v['listen_score1']?$v['listen_score1']:'';//督导评分1
				$list[$k]['listen_score2'] = $v['listen_score2']?$v['listen_score2']:'';//督导评分2
				$list[$k]['listen_score3'] = $v['listen_score3']?$v['listen_score3']:'';//督导评分3
				$list[$k]['listen_score4'] = $v['listen_score4']?$v['listen_score4']:'';//督导评分4
				$list[$k]['listen_score5'] = $avg;//督导评分5

			}
		}
		return $list;

	}

	//附件4商贸学校督导评分
	public function fujian4($Qualitydetail){
		$start_time = strtotime($Qualitydetail['start_month']);
		$end_time = strtotime($Qualitydetail['end_month']);
		$year = date("Y",strtotime($Qualitydetail['start_month']));
		$listen_type = $Qualitydetail['listen_type'];
		// $sql = "select `t`.`id` AS `id`,`t`.`usercode` AS `usercode`,`t`.`username` AS `username`,`t`.`academic_year` AS `academic_year`,`t`.`course_units` AS `course_units`,`atterd2_1`.`listen_score1` AS `listen_score1`,`atterd2_2`.`listen_score2` AS `listen_score2`,`atterd2_3`.`listen_score3` AS `listen_score3`,`atterd2_4`.`listen_score4` AS `listen_score4`,`atterd1`.`listen_score5` AS `listen_score5`,`atterd1_1`.`listen_score6` AS `listen_score6` from ((((((`zjipc_supervision`.`db_teacher_info` `t` left join (select `zjipc_supervision`.`db_attend`.`teacher_code` AS `teacher_code`,`zjipc_supervision`.`db_attend`.`teacher_name` AS `teacher_name`,`zjipc_supervision`.`db_attend`.`listen_name` AS `listen_name`,group_concat(`zjipc_supervision`.`db_attend`.`score` order by field(`zjipc_supervision`.`db_attend`.`listen_name`,'张凤珍','其他') ASC separator ',') AS `listen_score5` from `zjipc_supervision`.`db_attend` where ((`zjipc_supervision`.`db_attend`.`status` = 2) and (`zjipc_supervision`.`db_attend`.`type` = 1) and (`zjipc_supervision`.`db_attend`.`listen_time` >= ".$start_time.") and (`zjipc_supervision`.`db_attend`.`listen_time` <= ".$end_time.") and (`zjipc_supervision`.`db_attend`.`listen_name` = '张凤珍') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '暂无工号') and (`zjipc_supervision`.`db_attend`.`listen_type` = '日常听课')) group by `zjipc_supervision`.`db_attend`.`teacher_code`) `atterd1` on(((`atterd1`.`teacher_code` = `t`.`usercode`) and (`atterd1`.`teacher_name` = `t`.`username`)))) left join (select `zjipc_supervision`.`db_attend`.`teacher_code` AS `teacher_code`,`zjipc_supervision`.`db_attend`.`teacher_name` AS `teacher_name`,`zjipc_supervision`.`db_attend`.`listen_name` AS `listen_name`,group_concat(`zjipc_supervision`.`db_attend`.`score` order by field(`zjipc_supervision`.`db_attend`.`listen_name`,'其他') ASC separator ',') AS `listen_score6` from `zjipc_supervision`.`db_attend` where ((`zjipc_supervision`.`db_attend`.`status` = 2) and (`zjipc_supervision`.`db_attend`.`type` = 1) and (`zjipc_supervision`.`db_attend`.`listen_time` >= ".$start_time.") and (`zjipc_supervision`.`db_attend`.`listen_time` <= ".$end_time.") and (`zjipc_supervision`.`db_attend`.`listen_name` <> '张凤珍') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '暂无工号') and (`zjipc_supervision`.`db_attend`.`listen_type` = '日常听课')) group by `zjipc_supervision`.`db_attend`.`teacher_code`) `atterd1_1` on(((`atterd1_1`.`teacher_code` = `t`.`usercode`) and (`atterd1_1`.`teacher_name` = `t`.`username`)))) left join (select `zjipc_supervision`.`db_attend`.`teacher_code` AS `teacher_code`,`zjipc_supervision`.`db_attend`.`teacher_name` AS `teacher_name`,`zjipc_supervision`.`db_attend`.`listen_name` AS `listen_name`,group_concat(`zjipc_supervision`.`db_attend`.`score` order by field(`zjipc_supervision`.`db_attend`.`listen_name`,'蒋伟','其他') ASC separator ',') AS `listen_score1` from `zjipc_supervision`.`db_attend` where ((`zjipc_supervision`.`db_attend`.`status` = 2) and (`zjipc_supervision`.`db_attend`.`type` = 2) and (`zjipc_supervision`.`db_attend`.`listen_time` >= ".$start_time.") and (`zjipc_supervision`.`db_attend`.`listen_time` <= ".$end_time.") and (`zjipc_supervision`.`db_attend`.`listen_name` = '蒋伟') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '暂无工号') and (`zjipc_supervision`.`db_attend`.`listen_type` = '日常听课')) group by `zjipc_supervision`.`db_attend`.`teacher_code`) `atterd2_1` on(((`atterd2_1`.`teacher_code` = `t`.`usercode`) and (`atterd2_1`.`teacher_name` = `t`.`username`)))) left join (select `zjipc_supervision`.`db_attend`.`teacher_code` AS `teacher_code`,`zjipc_supervision`.`db_attend`.`teacher_name` AS `teacher_name`,`zjipc_supervision`.`db_attend`.`listen_name` AS `listen_name`,group_concat(`zjipc_supervision`.`db_attend`.`score` order by field(`zjipc_supervision`.`db_attend`.`listen_name`,'蔡小哩','其他') ASC separator ',') AS `listen_score2` from `zjipc_supervision`.`db_attend` where ((`zjipc_supervision`.`db_attend`.`status` = 2) and (`zjipc_supervision`.`db_attend`.`type` = 2) and (`zjipc_supervision`.`db_attend`.`listen_time` >= ".$start_time.") and (`zjipc_supervision`.`db_attend`.`listen_time` <= ".$end_time.") and (`zjipc_supervision`.`db_attend`.`listen_name` = '蔡小哩') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '暂无工号') and (`zjipc_supervision`.`db_attend`.`listen_type` = '日常听课')) group by `zjipc_supervision`.`db_attend`.`teacher_code`) `atterd2_2` on(((`atterd2_2`.`teacher_code` = `t`.`usercode`) and (`atterd2_2`.`teacher_name` = `t`.`username`)))) left join (select `zjipc_supervision`.`db_attend`.`teacher_code` AS `teacher_code`,`zjipc_supervision`.`db_attend`.`teacher_name` AS `teacher_name`,`zjipc_supervision`.`db_attend`.`listen_name` AS `listen_name`,group_concat(`zjipc_supervision`.`db_attend`.`score` order by field(`zjipc_supervision`.`db_attend`.`listen_name`,'王怀军','其他') ASC separator ',') AS `listen_score3` from `zjipc_supervision`.`db_attend` where ((`zjipc_supervision`.`db_attend`.`status` = 2) and (`zjipc_supervision`.`db_attend`.`type` = 2) and (`zjipc_supervision`.`db_attend`.`listen_time` >= ".$start_time.") and (`zjipc_supervision`.`db_attend`.`listen_time` <= ".$end_time.") and (`zjipc_supervision`.`db_attend`.`listen_name` = '王怀军') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '暂无工号') and (`zjipc_supervision`.`db_attend`.`listen_type` = '日常听课')) group by `zjipc_supervision`.`db_attend`.`teacher_code`) `atterd2_3` on(((`atterd2_3`.`teacher_code` = `t`.`usercode`) and (`atterd2_3`.`teacher_name` = `t`.`username`)))) left join (select `zjipc_supervision`.`db_attend`.`teacher_code` AS `teacher_code`,`zjipc_supervision`.`db_attend`.`teacher_name` AS `teacher_name`,`zjipc_supervision`.`db_attend`.`listen_name` AS `listen_name`,group_concat(`zjipc_supervision`.`db_attend`.`score` order by field(`zjipc_supervision`.`db_attend`.`listen_name`,'其他') ASC separator ',') AS `listen_score4` from `zjipc_supervision`.`db_attend` where ((`zjipc_supervision`.`db_attend`.`status` = 2) and (`zjipc_supervision`.`db_attend`.`type` = 2) and (`zjipc_supervision`.`db_attend`.`listen_time` >= ".$start_time.") and (`zjipc_supervision`.`db_attend`.`listen_time` <= ".$end_time.") and (`zjipc_supervision`.`db_attend`.`listen_name` not in ('蒋伟','蔡小哩','王怀军')) and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '暂无工号') and (`zjipc_supervision`.`db_attend`.`listen_type` = '日常听课')) group by `zjipc_supervision`.`db_attend`.`teacher_code`) `atterd2_4` on(((`atterd2_4`.`teacher_code` = `t`.`usercode`) and (`atterd2_4`.`teacher_name` = `t`.`username`)))) where ((`t`.`academic_year` = ".$year.") and (`t`.`course_units` = '商贸学院')) order by `t`.`id`";
		// $list = $this->CI->Teacher_info_model->query($sql);
		$dudaolist = array('二级-蒋伟','二级-蔡小哩','二级-崔宏芳','二级-其他','校-张凤珍','校-其他');
		$erjidudao ="'蒋伟','蔡小哩','崔宏芳'";
		$xiaodudao ="'张凤珍'";
		$danwei = '商贸学院';
		$list = $this->getDuDaoScoreSql($start_time,$end_time,$year,$dudaolist,$erjidudao,$xiaodudao,$danwei,$listen_type);
		// var_dump($list);
		if($list){
			foreach($list as $k=>$v){
				$a = array();
				if($v['listen_score1']){
				$a[] = explode(",",$v['listen_score1']);
				}
				if($v['listen_score2']){
				$a[] = explode(",",$v['listen_score2']);
				}
				if($v['listen_score3']){
					$a[] = explode(",",$v['listen_score3']);
				}
				if($v['listen_score4']){
					$a[] = explode(",",$v['listen_score4']);
				}
				if($v['listen_score5']){
					$a[] = explode(",",$v['listen_score5']);
				}
				if($v['listen_score6']){
					$a[] = explode(",",$v['listen_score6']);
				}

				$new_a = arrayTwo_One($a);//将二维数组转为一维数组导出
		
				if($new_a){
					$avg = round(array_sum($new_a)/count($new_a),2) ;
				}else{
					$avg = '';
				}
				$list[$k]['key'] = $k+1;
				$list[$k]['listen_score1'] = $v['listen_score1']?$v['listen_score1']:'';//督导评分1
				$list[$k]['listen_score2'] = $v['listen_score2']?$v['listen_score2']:'';//督导评分2
				$list[$k]['listen_score3'] = $v['listen_score3']?$v['listen_score3']:'';//督导评分3
				$list[$k]['listen_score4'] = $v['listen_score4']?$v['listen_score4']:'';//督导评分4
				$list[$k]['listen_score5'] = $v['listen_score5']?$v['listen_score5']:'';//督导评分4
				$list[$k]['listen_score6'] = $v['listen_score6']?$v['listen_score6']:'';//督导评分4
				$list[$k]['listen_score7'] = $avg;//督导评分5
			}
		}
		return $list;


	}
	//附件5交通学院
	public function fujian5($Qualitydetail){
		$start_time = strtotime($Qualitydetail['start_month']);
		$end_time = strtotime($Qualitydetail['end_month']);
		$year = date("Y",strtotime($Qualitydetail['start_month']));
		$listen_type = $Qualitydetail['listen_type'];
		// $sql = "select `t`.`id` AS `id`,`t`.`usercode` AS `usercode`,`t`.`username` AS `username`,`t`.`academic_year` AS `academic_year`,`t`.`course_units` AS `course_units`,`atterd2_1`.`listen_score1` AS `listen_score1`,`atterd2_2`.`listen_score2` AS `listen_score2`,`atterd2_3`.`listen_score3` AS `listen_score3`,`atterd2_4`.`listen_score4` AS `listen_score4`,`atterd1`.`listen_score5` AS `listen_score5` from (((((`zjipc_supervision`.`db_teacher_info` `t` left join (select `zjipc_supervision`.`db_attend`.`teacher_code` AS `teacher_code`,`zjipc_supervision`.`db_attend`.`teacher_name` AS `teacher_name`,`zjipc_supervision`.`db_attend`.`listen_name` AS `listen_name`,group_concat(`zjipc_supervision`.`db_attend`.`score` order by field(`zjipc_supervision`.`db_attend`.`listen_name`,'何柏海','其他') ASC separator ',') AS `listen_score5` from `zjipc_supervision`.`db_attend` where ((`zjipc_supervision`.`db_attend`.`status` = 2) and (`zjipc_supervision`.`db_attend`.`type` = 1) and (`zjipc_supervision`.`db_attend`.`listen_time` >= ".$start_time.") and (`zjipc_supervision`.`db_attend`.`listen_time` <= ".$end_time.") and (`zjipc_supervision`.`db_attend`.`listen_name` = '何柏海') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '暂无工号') and (`zjipc_supervision`.`db_attend`.`listen_type` = '日常听课')) group by `zjipc_supervision`.`db_attend`.`teacher_code`) `atterd1` on(((`atterd1`.`teacher_code` = `t`.`usercode`) and (`atterd1`.`teacher_name` = `t`.`username`)))) left join (select `zjipc_supervision`.`db_attend`.`teacher_code` AS `teacher_code`,`zjipc_supervision`.`db_attend`.`teacher_name` AS `teacher_name`,`zjipc_supervision`.`db_attend`.`listen_name` AS `listen_name`,group_concat(`zjipc_supervision`.`db_attend`.`score` order by field(`zjipc_supervision`.`db_attend`.`listen_name`,'叶建美','其他') ASC separator ',') AS `listen_score1` from `zjipc_supervision`.`db_attend` where ((`zjipc_supervision`.`db_attend`.`status` = 2) and (`zjipc_supervision`.`db_attend`.`type` = 2) and (`zjipc_supervision`.`db_attend`.`listen_time` >= ".$start_time.") and (`zjipc_supervision`.`db_attend`.`listen_time` <= ".$end_time.") and (`zjipc_supervision`.`db_attend`.`listen_name` = '叶建美') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '暂无工号') and (`zjipc_supervision`.`db_attend`.`listen_type` = '日常听课')) group by `zjipc_supervision`.`db_attend`.`teacher_code`) `atterd2_1` on(((`atterd2_1`.`teacher_code` = `t`.`usercode`) and (`atterd2_1`.`teacher_name` = `t`.`username`)))) left join (select `zjipc_supervision`.`db_attend`.`teacher_code` AS `teacher_code`,`zjipc_supervision`.`db_attend`.`teacher_name` AS `teacher_name`,`zjipc_supervision`.`db_attend`.`listen_name` AS `listen_name`,group_concat(`zjipc_supervision`.`db_attend`.`score` order by field(`zjipc_supervision`.`db_attend`.`listen_name`,'解后循','其他') ASC separator ',') AS `listen_score2` from `zjipc_supervision`.`db_attend` where ((`zjipc_supervision`.`db_attend`.`status` = 2) and (`zjipc_supervision`.`db_attend`.`type` = 2) and (`zjipc_supervision`.`db_attend`.`listen_time` >= ".$start_time.") and (`zjipc_supervision`.`db_attend`.`listen_time` <= ".$end_time.") and (`zjipc_supervision`.`db_attend`.`listen_name` = '解后循') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '暂无工号') and (`zjipc_supervision`.`db_attend`.`listen_type` = '日常听课')) group by `zjipc_supervision`.`db_attend`.`teacher_code`) `atterd2_2` on(((`atterd2_2`.`teacher_code` = `t`.`usercode`) and (`atterd2_2`.`teacher_name` = `t`.`username`)))) left join (select `zjipc_supervision`.`db_attend`.`teacher_code` AS `teacher_code`,`zjipc_supervision`.`db_attend`.`teacher_name` AS `teacher_name`,`zjipc_supervision`.`db_attend`.`listen_name` AS `listen_name`,group_concat(`zjipc_supervision`.`db_attend`.`score` order by field(`zjipc_supervision`.`db_attend`.`listen_name`,'贾春扬','其他') ASC separator ',') AS `listen_score3` from `zjipc_supervision`.`db_attend` where ((`zjipc_supervision`.`db_attend`.`status` = 2) and (`zjipc_supervision`.`db_attend`.`type` = 2) and (`zjipc_supervision`.`db_attend`.`listen_time` >= ".$start_time.") and (`zjipc_supervision`.`db_attend`.`listen_time` <= ".$end_time.") and (`zjipc_supervision`.`db_attend`.`listen_name` = '贾春扬') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '暂无工号') and (`zjipc_supervision`.`db_attend`.`listen_type` = '日常听课')) group by `zjipc_supervision`.`db_attend`.`teacher_code`) `atterd2_3` on(((`atterd2_3`.`teacher_code` = `t`.`usercode`) and (`atterd2_3`.`teacher_name` = `t`.`username`)))) left join (select `zjipc_supervision`.`db_attend`.`teacher_code` AS `teacher_code`,`zjipc_supervision`.`db_attend`.`teacher_name` AS `teacher_name`,`zjipc_supervision`.`db_attend`.`listen_name` AS `listen_name`,group_concat(`zjipc_supervision`.`db_attend`.`score` order by field(`zjipc_supervision`.`db_attend`.`listen_name`,'其他') ASC separator ',') AS `listen_score4` from `zjipc_supervision`.`db_attend` where ((`zjipc_supervision`.`db_attend`.`status` = 2) and (`zjipc_supervision`.`db_attend`.`type` = 2) and (`zjipc_supervision`.`db_attend`.`listen_time` >= ".$start_time.") and (`zjipc_supervision`.`db_attend`.`listen_time` <= ".$end_time.") and (`zjipc_supervision`.`db_attend`.`listen_name` not in ('叶建美','解后循','贾春扬')) and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '暂无工号') and (`zjipc_supervision`.`db_attend`.`listen_type` = '日常听课')) group by `zjipc_supervision`.`db_attend`.`teacher_code`) `atterd2_4` on(((`atterd2_4`.`teacher_code` = `t`.`usercode`) and (`atterd2_4`.`teacher_name` = `t`.`username`)))) where ((`t`.`academic_year` = ".$year.") and (`t`.`course_units` = '交通学院')) order by `t`.`id`";
		// $list = $this->CI->Teacher_info_model->query($sql);
		$dudaolist = array('二级-叶建美','二级-解后循','二级-贾春扬','二级-其他','校-何柏海');
		$erjidudao ="'叶建美','解后循','贾春扬'";
		$xiaodudao ="'何柏海'";
		$danwei = '交通学院';
		$list = $this->getDuDaoScoreSql($start_time,$end_time,$year,$dudaolist,$erjidudao,$xiaodudao,$danwei,$listen_type);

		if($list){
			foreach($list as $k=>$v){
				$a = array();
				if($v['listen_score1']){
				$a[] = explode(",",$v['listen_score1']);
				}
				if($v['listen_score2']){
				$a[] = explode(",",$v['listen_score2']);
				}
				if($v['listen_score3']){
					$a[] = explode(",",$v['listen_score3']);
				}
				if($v['listen_score4']){
					$a[] = explode(",",$v['listen_score4']);
				}
				if($v['listen_score5']){
					$a[] = explode(",",$v['listen_score5']);
				}
				$new_a = arrayTwo_One($a);//将二维数组转为一维数组导出
		
				if($new_a){
					$avg = round(array_sum($new_a)/count($new_a),2) ;
				}else{
					$avg = '';
				}
				$list[$k]['key'] = $k+1;
				$list[$k]['listen_score1'] = $v['listen_score1']?$v['listen_score1']:'';//督导评分1
				$list[$k]['listen_score2'] = $v['listen_score2']?$v['listen_score2']:'';//督导评分2
				$list[$k]['listen_score3'] = $v['listen_score3']?$v['listen_score3']:'';//督导评分3
				$list[$k]['listen_score4'] = $v['listen_score4']?$v['listen_score4']:'';//督导评分4
				$list[$k]['listen_score5'] = $v['listen_score5']?$v['listen_score5']:'';//督导评分4
				$list[$k]['listen_score6'] = $avg;//督导评分5
			}
		}
		return $list;
	}
	//附件6设计学院
	public function fujian6($Qualitydetail){
		$start_time = strtotime($Qualitydetail['start_month']);
		$end_time = strtotime($Qualitydetail['end_month']);
		$year = date("Y",strtotime($Qualitydetail['start_month']));
		$listen_type = $Qualitydetail['listen_type'];
		// $sql = "select `t`.`id` AS `id`,`t`.`usercode` AS `usercode`,`t`.`username` AS `username`,`t`.`academic_year` AS `academic_year`,`t`.`course_units` AS `course_units`,`atterd2_1`.`listen_score1` AS `listen_score1`,`atterd2_2`.`listen_score2` AS `listen_score2`,`atterd2_3`.`listen_score3` AS `listen_score3`,`atterd2_4`.`listen_score4` AS `listen_score4`,`atterd2_5`.`listen_score5` AS `listen_score5`,`atterd1_1`.`listen_score6` AS `listen_score6`,`atterd1`.`listen_score7` AS `listen_score7` from (((((((`zjipc_supervision`.`db_teacher_info` `t` left join (select `zjipc_supervision`.`db_attend`.`teacher_code` AS `teacher_code`,`zjipc_supervision`.`db_attend`.`teacher_name` AS `teacher_name`,`zjipc_supervision`.`db_attend`.`listen_name` AS `listen_name`,group_concat(`zjipc_supervision`.`db_attend`.`score` order by field(`zjipc_supervision`.`db_attend`.`listen_name`,'冯根尧','其他') ASC separator ',') AS `listen_score7` from `zjipc_supervision`.`db_attend` where ((`zjipc_supervision`.`db_attend`.`status` = 2) and (`zjipc_supervision`.`db_attend`.`type` = 1) and (`zjipc_supervision`.`db_attend`.`listen_time` >= ".$start_time.") and (`zjipc_supervision`.`db_attend`.`listen_time` <= ".$end_time.") and (`zjipc_supervision`.`db_attend`.`listen_name` = '冯根尧') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '暂无工号') and (`zjipc_supervision`.`db_attend`.`listen_type` = '日常听课')) group by `zjipc_supervision`.`db_attend`.`teacher_code`) `atterd1` on(((`atterd1`.`teacher_code` = `t`.`usercode`) and (`atterd1`.`teacher_name` = `t`.`username`)))) left join (select `zjipc_supervision`.`db_attend`.`teacher_code` AS `teacher_code`,`zjipc_supervision`.`db_attend`.`teacher_name` AS `teacher_name`,`zjipc_supervision`.`db_attend`.`listen_name` AS `listen_name`,group_concat(`zjipc_supervision`.`db_attend`.`score` order by field(`zjipc_supervision`.`db_attend`.`listen_name`,'其他') ASC separator ',') AS `listen_score6` from `zjipc_supervision`.`db_attend` where ((`zjipc_supervision`.`db_attend`.`status` = 2) and (`zjipc_supervision`.`db_attend`.`type` = 1) and (`zjipc_supervision`.`db_attend`.`listen_time` >= ".$start_time.") and (`zjipc_supervision`.`db_attend`.`listen_time` <= ".$end_time.") and (`zjipc_supervision`.`db_attend`.`listen_name` <> '冯根尧') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '暂无工号') and (`zjipc_supervision`.`db_attend`.`listen_type` = '日常听课')) group by `zjipc_supervision`.`db_attend`.`teacher_code`) `atterd1_1` on(((`atterd1_1`.`teacher_code` = `t`.`usercode`) and (`atterd1_1`.`teacher_name` = `t`.`username`)))) left join (select `zjipc_supervision`.`db_attend`.`teacher_code` AS `teacher_code`,`zjipc_supervision`.`db_attend`.`teacher_name` AS `teacher_name`,`zjipc_supervision`.`db_attend`.`listen_name` AS `listen_name`,group_concat(`zjipc_supervision`.`db_attend`.`score` order by field(`zjipc_supervision`.`db_attend`.`listen_name`,'陈军','其他') ASC separator ',') AS `listen_score1` from `zjipc_supervision`.`db_attend` where ((`zjipc_supervision`.`db_attend`.`status` = 2) and (`zjipc_supervision`.`db_attend`.`type` = 2) and (`zjipc_supervision`.`db_attend`.`listen_time` >= ".$start_time.") and (`zjipc_supervision`.`db_attend`.`listen_time` <= ".$end_time.") and (`zjipc_supervision`.`db_attend`.`listen_name` = '陈军') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '暂无工号') and (`zjipc_supervision`.`db_attend`.`listen_type` = '日常听课')) group by `zjipc_supervision`.`db_attend`.`teacher_code`) `atterd2_1` on(((`atterd2_1`.`teacher_code` = `t`.`usercode`) and (`atterd2_1`.`teacher_name` = `t`.`username`)))) left join (select `zjipc_supervision`.`db_attend`.`teacher_code` AS `teacher_code`,`zjipc_supervision`.`db_attend`.`teacher_name` AS `teacher_name`,`zjipc_supervision`.`db_attend`.`listen_name` AS `listen_name`,group_concat(`zjipc_supervision`.`db_attend`.`score` order by field(`zjipc_supervision`.`db_attend`.`listen_name`,'王常亮','其他') ASC separator ',') AS `listen_score2` from `zjipc_supervision`.`db_attend` where ((`zjipc_supervision`.`db_attend`.`status` = 2) and (`zjipc_supervision`.`db_attend`.`type` = 2) and (`zjipc_supervision`.`db_attend`.`listen_time` >= ".$start_time.") and (`zjipc_supervision`.`db_attend`.`listen_time` <= ".$end_time.") and (`zjipc_supervision`.`db_attend`.`listen_name` = '王常亮') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '暂无工号') and (`zjipc_supervision`.`db_attend`.`listen_type` = '日常听课')) group by `zjipc_supervision`.`db_attend`.`teacher_code`) `atterd2_2` on(((`atterd2_2`.`teacher_code` = `t`.`usercode`) and (`atterd2_2`.`teacher_name` = `t`.`username`)))) left join (select `zjipc_supervision`.`db_attend`.`teacher_code` AS `teacher_code`,`zjipc_supervision`.`db_attend`.`teacher_name` AS `teacher_name`,`zjipc_supervision`.`db_attend`.`listen_name` AS `listen_name`,group_concat(`zjipc_supervision`.`db_attend`.`score` order by field(`zjipc_supervision`.`db_attend`.`listen_name`,'张春琴','其他') ASC separator ',') AS `listen_score3` from `zjipc_supervision`.`db_attend` where ((`zjipc_supervision`.`db_attend`.`status` = 2) and (`zjipc_supervision`.`db_attend`.`type` = 2) and (`zjipc_supervision`.`db_attend`.`listen_time` >= ".$start_time.") and (`zjipc_supervision`.`db_attend`.`listen_time` <= ".$end_time.") and (`zjipc_supervision`.`db_attend`.`listen_name` = '张春琴') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '暂无工号') and (`zjipc_supervision`.`db_attend`.`listen_type` = '日常听课')) group by `zjipc_supervision`.`db_attend`.`teacher_code`) `atterd2_3` on(((`atterd2_3`.`teacher_code` = `t`.`usercode`) and (`atterd2_3`.`teacher_name` = `t`.`username`)))) left join (select `zjipc_supervision`.`db_attend`.`teacher_code` AS `teacher_code`,`zjipc_supervision`.`db_attend`.`teacher_name` AS `teacher_name`,`zjipc_supervision`.`db_attend`.`listen_name` AS `listen_name`,group_concat(`zjipc_supervision`.`db_attend`.`score` order by field(`zjipc_supervision`.`db_attend`.`listen_name`,'邢红刚','其他') ASC separator ',') AS `listen_score4` from `zjipc_supervision`.`db_attend` where ((`zjipc_supervision`.`db_attend`.`status` = 2) and (`zjipc_supervision`.`db_attend`.`type` = 2) and (`zjipc_supervision`.`db_attend`.`listen_time` >= ".$start_time.") and (`zjipc_supervision`.`db_attend`.`listen_time` <= ".$end_time.") and (`zjipc_supervision`.`db_attend`.`listen_name` = '邢红刚') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '暂无工号') and (`zjipc_supervision`.`db_attend`.`listen_type` = '日常听课')) group by `zjipc_supervision`.`db_attend`.`teacher_code`) `atterd2_4` on(((`atterd2_4`.`teacher_code` = `t`.`usercode`) and (`atterd2_4`.`teacher_name` = `t`.`username`)))) left join (select `zjipc_supervision`.`db_attend`.`teacher_code` AS `teacher_code`,`zjipc_supervision`.`db_attend`.`teacher_name` AS `teacher_name`,`zjipc_supervision`.`db_attend`.`listen_name` AS `listen_name`,group_concat(`zjipc_supervision`.`db_attend`.`score` order by field(`zjipc_supervision`.`db_attend`.`listen_name`,'其他') ASC separator ',') AS `listen_score5` from `zjipc_supervision`.`db_attend` where ((`zjipc_supervision`.`db_attend`.`status` = 2) and (`zjipc_supervision`.`db_attend`.`type` = 2) and (`zjipc_supervision`.`db_attend`.`listen_time` >= ".$start_time.") and (`zjipc_supervision`.`db_attend`.`listen_time` <= ".$end_time.") and (`zjipc_supervision`.`db_attend`.`listen_name` not in ('陈军','王常亮','张春琴','邢红刚')) and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '暂无工号') and (`zjipc_supervision`.`db_attend`.`listen_type` = '日常听课')) group by `zjipc_supervision`.`db_attend`.`teacher_code`) `atterd2_5` on(((`atterd2_5`.`teacher_code` = `t`.`usercode`) and (`atterd2_5`.`teacher_name` = `t`.`username`)))) where ((`t`.`academic_year` = ".$year.") and (`t`.`course_units` = '设计与艺术学院（腾讯云国际互联网学院）')) order by `t`.`id`";
		// $list = $this->CI->Teacher_info_model->query($sql);
		$dudaolist = array('二级-陈军','二级-王常亮','二级-张春琴','二级-邢红刚','二级-其他','校-其他','校-冯根尧');
		$erjidudao ="'陈军','王常亮','张春琴','邢红刚'";
		$xiaodudao ="'冯根尧'";
		$danwei = '设计与艺术学院（腾讯云国际互联网学院）';
		$list = $this->getDuDaoScoreSql($start_time,$end_time,$year,$dudaolist,$erjidudao,$xiaodudao,$danwei,$listen_type);

		if($list){
			foreach($list as $k=>$v){
				$a = array();
				if($v['listen_score1']){
				$a[] = explode(",",$v['listen_score1']);
				}
				if($v['listen_score2']){
				$a[] = explode(",",$v['listen_score2']);
				}
				if($v['listen_score3']){
					$a[] = explode(",",$v['listen_score3']);
				}
				if($v['listen_score4']){
					$a[] = explode(",",$v['listen_score4']);
				}
				if($v['listen_score5']){
					$a[] = explode(",",$v['listen_score5']);
				}
				if($v['listen_score6']){
					$a[] = explode(",",$v['listen_score6']);
				}
				if($v['listen_score7']){
					$a[] = explode(",",$v['listen_score7']);
				}
				$new_a = arrayTwo_One($a);//将二维数组转为一维数组导出
		
				if($new_a){
					$avg = round(array_sum($new_a)/count($new_a),2) ;
				}else{
					$avg = '';
				}
				$list[$k]['key'] = $k+1;
				$list[$k]['listen_score1'] = $v['listen_score1']?$v['listen_score1']:'';//督导评分1
				$list[$k]['listen_score2'] = $v['listen_score2']?$v['listen_score2']:'';//督导评分2
				$list[$k]['listen_score3'] = $v['listen_score3']?$v['listen_score3']:'';//督导评分3
				$list[$k]['listen_score4'] = $v['listen_score4']?$v['listen_score4']:'';//督导评分4
				$list[$k]['listen_score5'] = $v['listen_score5']?$v['listen_score5']:'';//督导评分4
				$list[$k]['listen_score6'] = $v['listen_score6']?$v['listen_score6']:'';//督导评分4
				$list[$k]['listen_score7'] = $v['listen_score7']?$v['listen_score7']:'';//督导评分4
				$list[$k]['listen_score8'] = $avg;//督导评分5
			}
		}
		return $list;

	}
	//附件7鉴湖学院
	public function fujian7($Qualitydetail){
		$start_time = strtotime($Qualitydetail['start_month']);
		$end_time = strtotime($Qualitydetail['end_month']);
		$year = date("Y",strtotime($Qualitydetail['start_month']));
		$listen_type = $Qualitydetail['listen_type'];
		// $sql = "select `t`.`id` AS `id`,`t`.`usercode` AS `usercode`,`t`.`username` AS `username`,`t`.`academic_year` AS `academic_year`,`t`.`course_units` AS `course_units`,`atterd2_1`.`listen_score1` AS `listen_score1`,`atterd2_2`.`listen_score2` AS `listen_score2`,`atterd2_3`.`listen_score3` AS `listen_score3`,`atterd2_4`.`listen_score4` AS `listen_score4`,`atterd1`.`listen_score5` AS `listen_score5`,`atterd1_1`.`listen_score6` AS `listen_score6` from ((((((`zjipc_supervision`.`db_teacher_info` `t` left join (select `zjipc_supervision`.`db_attend`.`teacher_code` AS `teacher_code`,`zjipc_supervision`.`db_attend`.`teacher_name` AS `teacher_name`,`zjipc_supervision`.`db_attend`.`listen_name` AS `listen_name`,group_concat(`zjipc_supervision`.`db_attend`.`score` order by field(`zjipc_supervision`.`db_attend`.`listen_name`,'何柏海','其他') ASC separator ',') AS `listen_score5` from `zjipc_supervision`.`db_attend` where ((`zjipc_supervision`.`db_attend`.`status` = 2) and (`zjipc_supervision`.`db_attend`.`type` = 1) and (`zjipc_supervision`.`db_attend`.`listen_time` >= ".$start_time.") and (`zjipc_supervision`.`db_attend`.`listen_time` <= ".$end_time.") and (`zjipc_supervision`.`db_attend`.`listen_name` = '何柏海') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '暂无工号') and (`zjipc_supervision`.`db_attend`.`listen_type` = '日常听课')) group by `zjipc_supervision`.`db_attend`.`teacher_code`) `atterd1` on(((`atterd1`.`teacher_code` = `t`.`usercode`) and (`atterd1`.`teacher_name` = `t`.`username`)))) left join (select `zjipc_supervision`.`db_attend`.`teacher_code` AS `teacher_code`,`zjipc_supervision`.`db_attend`.`teacher_name` AS `teacher_name`,`zjipc_supervision`.`db_attend`.`listen_name` AS `listen_name`,group_concat(`zjipc_supervision`.`db_attend`.`score` order by field(`zjipc_supervision`.`db_attend`.`listen_name`,'其他') ASC separator ',') AS `listen_score6` from `zjipc_supervision`.`db_attend` where ((`zjipc_supervision`.`db_attend`.`status` = 2) and (`zjipc_supervision`.`db_attend`.`type` = 1) and (`zjipc_supervision`.`db_attend`.`listen_time` >= ".$start_time.") and (`zjipc_supervision`.`db_attend`.`listen_time` <= ".$end_time.") and (`zjipc_supervision`.`db_attend`.`listen_name` <> '何柏海') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '暂无工号') and (`zjipc_supervision`.`db_attend`.`listen_type` = '日常听课')) group by `zjipc_supervision`.`db_attend`.`teacher_code`) `atterd1_1` on(((`atterd1_1`.`teacher_code` = `t`.`usercode`) and (`atterd1_1`.`teacher_name` = `t`.`username`)))) left join (select `zjipc_supervision`.`db_attend`.`teacher_code` AS `teacher_code`,`zjipc_supervision`.`db_attend`.`teacher_name` AS `teacher_name`,`zjipc_supervision`.`db_attend`.`listen_name` AS `listen_name`,group_concat(`zjipc_supervision`.`db_attend`.`score` order by field(`zjipc_supervision`.`db_attend`.`listen_name`,'李志刚','其他') ASC separator ',') AS `listen_score1` from `zjipc_supervision`.`db_attend` where ((`zjipc_supervision`.`db_attend`.`status` = 2) and (`zjipc_supervision`.`db_attend`.`type` = 2) and (`zjipc_supervision`.`db_attend`.`listen_time` >= ".$start_time.") and (`zjipc_supervision`.`db_attend`.`listen_time` <= ".$end_time.") and (`zjipc_supervision`.`db_attend`.`listen_name` = '李志刚') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '暂无工号') and (`zjipc_supervision`.`db_attend`.`listen_type` = '日常听课')) group by `zjipc_supervision`.`db_attend`.`teacher_code`) `atterd2_1` on(((`atterd2_1`.`teacher_code` = `t`.`usercode`) and (`atterd2_1`.`teacher_name` = `t`.`username`)))) left join (select `zjipc_supervision`.`db_attend`.`teacher_code` AS `teacher_code`,`zjipc_supervision`.`db_attend`.`teacher_name` AS `teacher_name`,`zjipc_supervision`.`db_attend`.`listen_name` AS `listen_name`,group_concat(`zjipc_supervision`.`db_attend`.`score` order by field(`zjipc_supervision`.`db_attend`.`listen_name`,'盛冠忠','其他') ASC separator ',') AS `listen_score2` from `zjipc_supervision`.`db_attend` where ((`zjipc_supervision`.`db_attend`.`status` = 2) and (`zjipc_supervision`.`db_attend`.`type` = 2) and (`zjipc_supervision`.`db_attend`.`listen_time` >= ".$start_time.") and (`zjipc_supervision`.`db_attend`.`listen_time` <= ".$end_time.") and (`zjipc_supervision`.`db_attend`.`listen_name` = '盛冠忠') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '暂无工号') and (`zjipc_supervision`.`db_attend`.`listen_type` = '日常听课')) group by `zjipc_supervision`.`db_attend`.`teacher_code`) `atterd2_2` on(((`atterd2_2`.`teacher_code` = `t`.`usercode`) and (`atterd2_2`.`teacher_name` = `t`.`username`)))) left join (select `zjipc_supervision`.`db_attend`.`teacher_code` AS `teacher_code`,`zjipc_supervision`.`db_attend`.`teacher_name` AS `teacher_name`,`zjipc_supervision`.`db_attend`.`listen_name` AS `listen_name`,group_concat(`zjipc_supervision`.`db_attend`.`score` order by field(`zjipc_supervision`.`db_attend`.`listen_name`,'段凯旋','其他') ASC separator ',') AS `listen_score3` from `zjipc_supervision`.`db_attend` where ((`zjipc_supervision`.`db_attend`.`status` = 2) and (`zjipc_supervision`.`db_attend`.`type` = 2) and (`zjipc_supervision`.`db_attend`.`listen_time` >= ".$start_time.") and (`zjipc_supervision`.`db_attend`.`listen_time` <= ".$end_time.") and (`zjipc_supervision`.`db_attend`.`listen_name` = '段凯旋') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '暂无工号') and (`zjipc_supervision`.`db_attend`.`listen_type` = '日常听课')) group by `zjipc_supervision`.`db_attend`.`teacher_code`) `atterd2_3` on(((`atterd2_3`.`teacher_code` = `t`.`usercode`) and (`atterd2_3`.`teacher_name` = `t`.`username`)))) left join (select `zjipc_supervision`.`db_attend`.`teacher_code` AS `teacher_code`,`zjipc_supervision`.`db_attend`.`teacher_name` AS `teacher_name`,`zjipc_supervision`.`db_attend`.`listen_name` AS `listen_name`,group_concat(`zjipc_supervision`.`db_attend`.`score` order by field(`zjipc_supervision`.`db_attend`.`listen_name`,'其他') ASC separator ',') AS `listen_score4` from `zjipc_supervision`.`db_attend` where ((`zjipc_supervision`.`db_attend`.`status` = 2) and (`zjipc_supervision`.`db_attend`.`type` = 2) and (`zjipc_supervision`.`db_attend`.`listen_time` >= ".$start_time.") and (`zjipc_supervision`.`db_attend`.`listen_time` <= ".$end_time.") and (`zjipc_supervision`.`db_attend`.`listen_name` not in ('李志刚','盛冠忠','段凯旋')) and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '暂无工号') and (`zjipc_supervision`.`db_attend`.`listen_type` = '日常听课')) group by `zjipc_supervision`.`db_attend`.`teacher_code`) `atterd2_4` on(((`atterd2_4`.`teacher_code` = `t`.`usercode`) and (`atterd2_4`.`teacher_name` = `t`.`username`)))) where ((`t`.`academic_year` = ".$year.") and (`t`.`course_units` = '鉴湖学院（纺织学院、黄酒学院、健康学院）')) order by `t`.`id`";
		// $list = $this->CI->Teacher_info_model->query($sql);
		$dudaolist = array('二级-李志刚','二级-盛冠忠','二级-段凯旋','二级-其他','校-何柏海','校-其他');
		$erjidudao ="'李志刚','盛冠忠','段凯旋'";
		$xiaodudao ="'何柏海'";
		$danwei = '鉴湖学院（纺织学院、黄酒学院、健康学院）';
		$list = $this->getDuDaoScoreSql($start_time,$end_time,$year,$dudaolist,$erjidudao,$xiaodudao,$danwei,$listen_type);
		
		if($list){
			foreach($list as $k=>$v){
				$a = array();
				if($v['listen_score1']){
				$a[] = explode(",",$v['listen_score1']);
				}
				if($v['listen_score2']){
				$a[] = explode(",",$v['listen_score2']);
				}
				if($v['listen_score3']){
					$a[] = explode(",",$v['listen_score3']);
				}
				if($v['listen_score4']){
					$a[] = explode(",",$v['listen_score4']);
				}
				if($v['listen_score5']){
					$a[] = explode(",",$v['listen_score5']);
				}
				if($v['listen_score6']){
					$a[] = explode(",",$v['listen_score6']);
				}

				$new_a = arrayTwo_One($a);//将二维数组转为一维数组导出
		
				if($new_a){
					$avg = round(array_sum($new_a)/count($new_a),2) ;
				}else{
					$avg = '';
				}
				$list[$k]['key'] = $k+1;
				$list[$k]['listen_score1'] = $v['listen_score1']?$v['listen_score1']:'';//督导评分1
				$list[$k]['listen_score2'] = $v['listen_score2']?$v['listen_score2']:'';//督导评分2
				$list[$k]['listen_score3'] = $v['listen_score3']?$v['listen_score3']:'';//督导评分3
				$list[$k]['listen_score4'] = $v['listen_score4']?$v['listen_score4']:'';//督导评分4
				$list[$k]['listen_score5'] = $v['listen_score5']?$v['listen_score5']:'';//督导评分4
				$list[$k]['listen_score6'] = $v['listen_score6']?$v['listen_score6']:'';//督导评分4
				$list[$k]['listen_score7'] = $avg;//督导评分5
			}
		}
		return $list;
	}
	//附件8
	public function fujian8($Qualitydetail){
		$start_time = strtotime($Qualitydetail['start_month']);
		$end_time = strtotime($Qualitydetail['end_month']);
		$year = date("Y",strtotime($Qualitydetail['start_month']));
		$listen_type = $Qualitydetail['listen_type'];
		// $sql = "select `t`.`id` AS `id`,`t`.`usercode` AS `usercode`,`t`.`username` AS `username`,`t`.`academic_year` AS `academic_year`,`t`.`course_units` AS `course_units`,`atterd2_1`.`listen_score1` AS `listen_score1`,`atterd2_2`.`listen_score2` AS `listen_score2`,`atterd2_3`.`listen_score3` AS `listen_score3`,`atterd2_4`.`listen_score4` AS `listen_score4`,`atterd2_5`.`listen_score5` AS `listen_score5`,`atterd2_6`.`listen_score6` AS `listen_score6`,`atterd1`.`listen_score7` AS `listen_score7`,`atterd1_1`.`listen_score8` AS `listen_score8` from ((((((((`zjipc_supervision`.`db_teacher_info` `t` left join (select `zjipc_supervision`.`db_attend`.`teacher_code` AS `teacher_code`,`zjipc_supervision`.`db_attend`.`teacher_name` AS `teacher_name`,`zjipc_supervision`.`db_attend`.`listen_name` AS `listen_name`,group_concat(`zjipc_supervision`.`db_attend`.`score` order by field(`zjipc_supervision`.`db_attend`.`listen_name`,'冯根尧','其他') ASC separator ',') AS `listen_score7` from `zjipc_supervision`.`db_attend` where ((`zjipc_supervision`.`db_attend`.`status` = 2) and (`zjipc_supervision`.`db_attend`.`type` = 1) and (`zjipc_supervision`.`db_attend`.`listen_time` >= ".$start_time.") and (`zjipc_supervision`.`db_attend`.`listen_time` <= ".$end_time.") and (`zjipc_supervision`.`db_attend`.`listen_name` = '冯根尧') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '暂无工号') and (`zjipc_supervision`.`db_attend`.`listen_type` = '日常听课')) group by `zjipc_supervision`.`db_attend`.`teacher_code`) `atterd1` on(((`atterd1`.`teacher_code` = `t`.`usercode`) and (`atterd1`.`teacher_name` = `t`.`username`)))) left join (select `zjipc_supervision`.`db_attend`.`teacher_code` AS `teacher_code`,`zjipc_supervision`.`db_attend`.`teacher_name` AS `teacher_name`,`zjipc_supervision`.`db_attend`.`listen_name` AS `listen_name`,group_concat(`zjipc_supervision`.`db_attend`.`score` order by field(`zjipc_supervision`.`db_attend`.`listen_name`,'其他') ASC separator ',') AS `listen_score8` from `zjipc_supervision`.`db_attend` where ((`zjipc_supervision`.`db_attend`.`status` = 2) and (`zjipc_supervision`.`db_attend`.`type` = 1) and (`zjipc_supervision`.`db_attend`.`listen_time` >= ".$start_time.") and (`zjipc_supervision`.`db_attend`.`listen_time` <= ".$end_time.") and (`zjipc_supervision`.`db_attend`.`listen_name` <> '冯根尧') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '暂无工号') and (`zjipc_supervision`.`db_attend`.`listen_type` = '日常听课')) group by `zjipc_supervision`.`db_attend`.`teacher_code`) `atterd1_1` on(((`atterd1_1`.`teacher_code` = `t`.`usercode`) and (`atterd1_1`.`teacher_name` = `t`.`username`)))) left join (select `zjipc_supervision`.`db_attend`.`teacher_code` AS `teacher_code`,`zjipc_supervision`.`db_attend`.`teacher_name` AS `teacher_name`,`zjipc_supervision`.`db_attend`.`listen_name` AS `listen_name`,group_concat(`zjipc_supervision`.`db_attend`.`score` order by field(`zjipc_supervision`.`db_attend`.`listen_name`,'张新改','其他') ASC separator ',') AS `listen_score1` from `zjipc_supervision`.`db_attend` where ((`zjipc_supervision`.`db_attend`.`status` = 2) and (`zjipc_supervision`.`db_attend`.`type` = 2) and (`zjipc_supervision`.`db_attend`.`listen_time` >= ".$start_time.") and (`zjipc_supervision`.`db_attend`.`listen_time` <= ".$end_time.") and (`zjipc_supervision`.`db_attend`.`listen_name` = '张新改') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '暂无工号') and (`zjipc_supervision`.`db_attend`.`listen_type` = '日常听课')) group by `zjipc_supervision`.`db_attend`.`teacher_code`) `atterd2_1` on(((`atterd2_1`.`teacher_code` = `t`.`usercode`) and (`atterd2_1`.`teacher_name` = `t`.`username`)))) left join (select `zjipc_supervision`.`db_attend`.`teacher_code` AS `teacher_code`,`zjipc_supervision`.`db_attend`.`teacher_name` AS `teacher_name`,`zjipc_supervision`.`db_attend`.`listen_name` AS `listen_name`,group_concat(`zjipc_supervision`.`db_attend`.`score` order by field(`zjipc_supervision`.`db_attend`.`listen_name`,'吴江','其他') ASC separator ',') AS `listen_score2` from `zjipc_supervision`.`db_attend` where ((`zjipc_supervision`.`db_attend`.`status` = 2) and (`zjipc_supervision`.`db_attend`.`type` = 2) and (`zjipc_supervision`.`db_attend`.`listen_time` >= ".$start_time.") and (`zjipc_supervision`.`db_attend`.`listen_time` <= ".$end_time.") and (`zjipc_supervision`.`db_attend`.`listen_name` = '吴江') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '暂无工号') and (`zjipc_supervision`.`db_attend`.`listen_type` = '日常听课')) group by `zjipc_supervision`.`db_attend`.`teacher_code`) `atterd2_2` on(((`atterd2_2`.`teacher_code` = `t`.`usercode`) and (`atterd2_2`.`teacher_name` = `t`.`username`)))) left join (select `zjipc_supervision`.`db_attend`.`teacher_code` AS `teacher_code`,`zjipc_supervision`.`db_attend`.`teacher_name` AS `teacher_name`,`zjipc_supervision`.`db_attend`.`listen_name` AS `listen_name`,group_concat(`zjipc_supervision`.`db_attend`.`score` order by field(`zjipc_supervision`.`db_attend`.`listen_name`,'赵伟良','其他') ASC separator ',') AS `listen_score3` from `zjipc_supervision`.`db_attend` where ((`zjipc_supervision`.`db_attend`.`status` = 2) and (`zjipc_supervision`.`db_attend`.`type` = 2) and (`zjipc_supervision`.`db_attend`.`listen_time` >= ".$start_time.") and (`zjipc_supervision`.`db_attend`.`listen_time` <= ".$end_time.") and (`zjipc_supervision`.`db_attend`.`listen_name` = '赵伟良') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '暂无工号') and (`zjipc_supervision`.`db_attend`.`listen_type` = '日常听课')) group by `zjipc_supervision`.`db_attend`.`teacher_code`) `atterd2_3` on(((`atterd2_3`.`teacher_code` = `t`.`usercode`) and (`atterd2_3`.`teacher_name` = `t`.`username`)))) left join (select `zjipc_supervision`.`db_attend`.`teacher_code` AS `teacher_code`,`zjipc_supervision`.`db_attend`.`teacher_name` AS `teacher_name`,`zjipc_supervision`.`db_attend`.`listen_name` AS `listen_name`,group_concat(`zjipc_supervision`.`db_attend`.`score` order by field(`zjipc_supervision`.`db_attend`.`listen_name`,'童燕华','其他') ASC separator ',') AS `listen_score4` from `zjipc_supervision`.`db_attend` where ((`zjipc_supervision`.`db_attend`.`status` = 2) and (`zjipc_supervision`.`db_attend`.`type` = 2) and (`zjipc_supervision`.`db_attend`.`listen_time` >= ".$start_time.") and (`zjipc_supervision`.`db_attend`.`listen_time` <= ".$end_time.") and (`zjipc_supervision`.`db_attend`.`listen_name` = '童燕华') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '暂无工号') and (`zjipc_supervision`.`db_attend`.`listen_type` = '日常听课')) group by `zjipc_supervision`.`db_attend`.`teacher_code`) `atterd2_4` on(((`atterd2_4`.`teacher_code` = `t`.`usercode`) and (`atterd2_4`.`teacher_name` = `t`.`username`)))) left join (select `zjipc_supervision`.`db_attend`.`teacher_code` AS `teacher_code`,`zjipc_supervision`.`db_attend`.`teacher_name` AS `teacher_name`,`zjipc_supervision`.`db_attend`.`listen_name` AS `listen_name`,group_concat(`zjipc_supervision`.`db_attend`.`score` order by field(`zjipc_supervision`.`db_attend`.`listen_name`,'闫振伟','其他') ASC separator ',') AS `listen_score5` from `zjipc_supervision`.`db_attend` where ((`zjipc_supervision`.`db_attend`.`status` = 2) and (`zjipc_supervision`.`db_attend`.`type` = 2) and (`zjipc_supervision`.`db_attend`.`listen_time` >= ".$start_time.") and (`zjipc_supervision`.`db_attend`.`listen_time` <= ".$end_time.") and (`zjipc_supervision`.`db_attend`.`listen_name` = '闫振伟') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '暂无工号') and (`zjipc_supervision`.`db_attend`.`listen_type` = '日常听课')) group by `zjipc_supervision`.`db_attend`.`teacher_code`) `atterd2_5` on(((`atterd2_5`.`teacher_code` = `t`.`usercode`) and (`atterd2_5`.`teacher_name` = `t`.`username`)))) left join (select `zjipc_supervision`.`db_attend`.`teacher_code` AS `teacher_code`,`zjipc_supervision`.`db_attend`.`teacher_name` AS `teacher_name`,`zjipc_supervision`.`db_attend`.`listen_name` AS `listen_name`,group_concat(`zjipc_supervision`.`db_attend`.`score` order by field(`zjipc_supervision`.`db_attend`.`listen_name`,'苏美岩','其他') ASC separator ',') AS `listen_score6` from `zjipc_supervision`.`db_attend` where ((`zjipc_supervision`.`db_attend`.`status` = 2) and (`zjipc_supervision`.`db_attend`.`type` = 2) and (`zjipc_supervision`.`db_attend`.`listen_time` >= ".$start_time.") and (`zjipc_supervision`.`db_attend`.`listen_time` <= ".$end_time.") and (`zjipc_supervision`.`db_attend`.`listen_name` = '苏美岩') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '') and (`zjipc_supervision`.`db_attend`.`teacher_code` <> '暂无工号') and (`zjipc_supervision`.`db_attend`.`listen_type` = '日常听课')) group by `zjipc_supervision`.`db_attend`.`teacher_code`) `atterd2_6` on(((`atterd2_6`.`teacher_code` = `t`.`usercode`) and (`atterd2_6`.`teacher_name` = `t`.`username`)))) where ((`t`.`academic_year` = ".$year.") and (`t`.`course_units` = '公共基础教学部（体育部）、马克思主义学院')) order by `t`.`id`";
		// $list = $this->CI->Teacher_info_model->query($sql);

		$dudaolist = array('二级-张新改','二级-吴江','二级-赵伟良','二级-童燕华','二级-闫振伟','二级-苏美岩','校-冯根尧','校-其他');
		$erjidudao ="'张新改','吴江','赵伟良','童燕华','闫振伟','苏美岩'";
		$xiaodudao ="'冯根尧'";
		$danwei = '公共基础教学部（体育部）、马克思主义学院';
		$list = $this->getDuDaoScoreSql($start_time,$end_time,$year,$dudaolist,$erjidudao,$xiaodudao,$danwei,$listen_type);

		if($list){
			foreach($list as $k=>$v){
				$a = array();
				if($v['listen_score1']){
				$a[] = explode(",",$v['listen_score1']);
				}
				if($v['listen_score2']){
				$a[] = explode(",",$v['listen_score2']);
				}
				if($v['listen_score3']){
					$a[] = explode(",",$v['listen_score3']);
				}
				if($v['listen_score4']){
					$a[] = explode(",",$v['listen_score4']);
				}
				if($v['listen_score5']){
					$a[] = explode(",",$v['listen_score5']);
				}
				if($v['listen_score6']){
					$a[] = explode(",",$v['listen_score6']);
				}
				if($v['listen_score7']){
					$a[] = explode(",",$v['listen_score7']);
				}
				if($v['listen_score8']){
					$a[] = explode(",",$v['listen_score8']);
				}

				$new_a = arrayTwo_One($a);//将二维数组转为一维数组导出
		
				if($new_a){
					$avg = round(array_sum($new_a)/count($new_a),2) ;
				}else{
					$avg = '';
				}
				$list[$k]['key'] = $k+1;
				$list[$k]['listen_score1'] = $v['listen_score1']?$v['listen_score1']:'';//督导评分1
				$list[$k]['listen_score2'] = $v['listen_score2']?$v['listen_score2']:'';//督导评分2
				$list[$k]['listen_score3'] = $v['listen_score3']?$v['listen_score3']:'';//督导评分3
				$list[$k]['listen_score4'] = $v['listen_score4']?$v['listen_score4']:'';//督导评分4
				$list[$k]['listen_score5'] = $v['listen_score5']?$v['listen_score5']:'';//督导评分5
				$list[$k]['listen_score6'] = $v['listen_score6']?$v['listen_score6']:'';//督导评分6
				$list[$k]['listen_score7'] = $v['listen_score7']?$v['listen_score7']:'';//督导评分7
				$list[$k]['listen_score8'] = $v['listen_score8']?$v['listen_score8']:'';//督导评分8
				$list[$k]['listen_score9'] = $avg;//督导评分5
			}
		}
		return $list;

	}


	//查询八个分院督导评分情况

	public function getDuDaoScoreSql($start_time,$end_time,$year,$dudaolist,$erjidudao,$xiaodudao,$danwei,$listen_type){
		$sql = "SELECT t.*,";
		$listen_score_key = "";
		foreach($dudaolist as $k=>$v){
			$listen_score_key .= ",listen_score".($k+1);
		}
		// var_dump($listen_score_key);
		$sql .= substr($listen_score_key,'1');
		$sql .= " FROM db_teacher_info t";

		foreach ($dudaolist as $k=>$v){
			$name = explode("-",$v);
			if($name[0] == '二级'){
				$type = '2';		
			}elseif($name[0] == '校'){
				$type = '1';
			}
			$list_name = $name[1] ;
			$sql .=" LEFT JOIN ( SELECT teacher_code, teacher_name, listen_name, group_concat( `score` ORDER BY field(`listen_name`, '".$list_name."', '其他' ) ASC SEPARATOR ',' ) as listen_score".($k+1);
			$sql .= " FROM db_attend WHERE `status` = ".AttendStatus::Submit[XPHEnum::Value]." AND type = ".$type." and listen_time >= ".$start_time." and listen_time <= ".$end_time."  and teacher_code <> '' and teacher_code <> '暂无工号' and listen_type = '".$listen_type."'";
			if($type == 2 && $list_name != "其他"){
				$sql .=" and listen_name = '".$list_name."'";
			}else if($type == 2 && $list_name == "其他"){
				$sql .=" and listen_name not in (".$erjidudao.")";
			}else if($type == 1 && $list_name != "其他"){
				$sql .=" and listen_name = '".$list_name."'";
			}elseif($type == 1 && $list_name == "其他"){
				$sql .=" and listen_name not in (".$xiaodudao.")";
			}
			$sql .= " GROUP BY teacher_code ) as atterd".$type."_".($k+1)." on atterd".$type."_".($k+1).".teacher_code=t.usercode and atterd".$type."_".($k+1).".teacher_name = t.username";
		}

		$sql .= " where t.academic_year = ".$year." and course_units = '".$danwei."' ORDER BY t.id asc";

		$list = $this->CI->Teacher_info_model->query($sql);
		return $list;
	}





}